logo
Tags down

shadow

SQL get the names of the columns that are stored in a different table


By : Jake Ganz
Date : October 14 2020, 02:22 PM
I wish this helpful for you I have an Article table with generic column names "PARAM1", "PARAM2", "PARAM3"... , The following code/logic can be used to complete your task:
code :
DROP TABLE IF EXISTS #DataSource;

CREATE TABLE #DataSource
(
    [Param01] INT
   ,[Param02] INT
   ,[Param03] INT
);

DECLARE @DataHeadings TABLE
(
    [ColumnID] INT
   ,[ColumnName] SYSNAME
);

INSERT INTO #DataSource ([Param01], [Param02], [Param03])
VALUES (1, 1, 1)
      ,(2, 2, 2)
      ,(3, 3, 3);

INSERT INTO @DataHeadings ([ColumnID], [ColumnName])
VALUES (1, 'name')
      ,(2, 'age')
      ,(3, 'login');

DECLARE @DynamicTSQLStatement NVARCHAR(MAX);


SET @DynamicTSQLStatement = N'SELECT ' + STUFF
                            (
                                (
                                    SELECT ',[Param' + RIGHT([ColumnID] + 100, 2) + '] AS [' + [ColumnName] + ']'
                                    FROM @DataHeadings
                                    ORDER BY [ColumnID]
                                    FOR XML PATH(''), TYPE
                                ).value('.', 'VARCHAR(MAX)')
                                ,1
                                ,1
                                ,''
                            )
                            + 'FROM #DataSource';

EXEC sp_executesql @DynamicTSQLStatement;


Share : facebook icon twitter icon

sql server - getting values from one table with columns' names stored in another table


By : user2717611
Date : March 29 2020, 07:55 AM
it helps some times I have several tables. These are somewhat simplified versions of the tables: , DDL for your data
code :
create table UsersTable(
    UserId int, FirstName varchar(100), LastName varchar(100), MiddleInit varchar(100),
    Suffix varchar(100), Age int, Position varchar(100));
insert UsersTable select 
'1','John','Graham','P.','Jr.','35','Analyst II' union all select
'2','Bill','Allen','T.','III','45','Programmer I' union all select
'3','Jenny','Smith','K.',null,'25','Systems Engineer' union all select
'4','Gina','Todd','J.',null,'55','Analyst II';
create table tabletypes(
    TableTypeId int, TableType varchar(100));
insert tabletypes select
1, 'Names' union all select
2, 'Positions' union all select
3, 'Age';

create table TableFields(
    FieldId int,TableTypeId int,FieldName varchar(100),Description varchar(100));
insert TableFields select
'1','1','FirstName','descr1' union all select
'2','1','LastName','descr2' union all select
'3','1','MiddleInit','descr3' union all select
'4','1','Suffix','descr4';

create table ModifiedUsersTable(
    Id int identity, userid int, fieldid int, value varchar(max));
declare @sql nvarchar(max)
select @sql = coalesce(@sql+ '
    union all
    ', '') + '
    select userid,' + right(FieldId,12) + ',' + quotename(fieldname) + '
    from userstable'
from tablefields
set @sql = @sql + '
    order by userid, 2'

-- print @sql -- uncomment to see the generated SQL
exec (@sql)

Using the names of columns stored within fields to retrieve data from a different table


By : shavul hameed
Date : March 29 2020, 07:55 AM
Hope this helps I was wondering if there exists code to accomplish the following in SQL-Server 2008? , You can use UNPIVOT table2 to access the data from the columns:
code :
select t1.id, t2.value
from table1 t1
left join 
(
  select value, col
  from table2
  unpivot
  (
    value
    for col in (col1, col2, col3)
  ) u
) t2
  on t1.name = t2.col
select t1.id, t2.value
from table1 t1
left join 
(
  select col1 value, 'col1' col
  from table2
  union all
  select col2 value, 'col2' col
  from table2
  union all
  select col3 value, 'col3' col
  from table2
) t2
  on t1.name = t2.col

R, data.table: How to assign values of some columns based on the names of other columns, which are stored in a character


By : G.Thiyagarajan
Date : March 29 2020, 07:55 AM
around this issue I have the following data table: , I would do
code :
dt[2:3, (names1) := .SD, .SDcols = names2]
dt[c(2,3), (names1) := mget(names2)]

R, data.table: Sum all columns whose names are stored in a vector


By : MicahA
Date : March 29 2020, 07:55 AM
like below fixes the issue From a data.table d such as for example
code :
d[, lapply(.SD, sum), by = group, .SDcols = varsToSum]

   group a  b
1:     1 3 23
2:     2 7 27

Sum columns of R data table, the names of which are stored in a vector


By : mrQ993
Date : March 29 2020, 07:55 AM
With these it helps I have the following data table: , You can use rowSums on the relevant subset :
code :
dt_test <- data.table::data.table(day = 1:3, a = c(1,1,1), b = c(2,2,2))
vecCols <- c("a", "b")

dt_test[,sums := rowSums(.SD[,vecCols, with = FALSE])]
dt_test[,sums := rowSums(.SD), .SDcols = vecCols]

dt_test
#>    day a b sums
#> 1:   1 1 2    3
#> 2:   2 1 2    3
#> 3:   3 1 2    3
Related Posts Related Posts :
  • Count distinct values in an SQL table for each hour in a day
  • If else in Where Clause SQL
  • Select multiple rows with matching id(s)
  • Oracle SQL query for Group By with Having clause
  • sql list of records within a path scope
  • Condition for MAX(Booking) within a specific period
  • Unable to query multiple tables via XML: Error occurred in XML processing
  • SQL first date for the first occurrence of a value
  • Update multiple columns Table from a select statement
  • Is it possible to find (in an ordered table) multiple rows in sequence?
  • Query to return month-wise count in PostgreSQL
  • adding years getting invalid number error in case statement only - ORA-01722
  • Insert into a table using multiple tables
  • How to create a SQL Server Pivot Table
  • My SQL - COUNT field on multitable group by other field
  • How to order by a different column and then fetch offset rows in SQL Server?
  • Query builder Eloquent using Eager Loading in Laravel
  • What is the complexity of the CUBE operator in SQL?
  • IIF statement MS ACCESS VBA SQL
  • SQL Server group by?
  • LIKE in Elasticsearch for large texts
  • SQL - dynamic sum based on dynamic date range
  • SQL Order By: Specific Value First, then Ordering?
  • SQL Developer - SELECT statement with AVG calculation displaying multiple averages?
  • Microsoft SQL Server : return only the rows with the most recent date for each unique ID
  • How to create a queue like structure in SQL Server
  • Cross apply on columns on SQL server. Syntax error near )
  • Select count of subquery results, along with another field
  • SQL Server 2016 - Five Column to single row
  • Insert zero values for unexisting groups in Redshift
  • SQL LIKE '%...' in vba HTTP request
  • SQL Developer - How do I get MAX & AVG to draw from one column and filter from themselves?
  • How to optimize SQL query with multiple selects
  • Postgresql 11 - Create Procedure to Execute COPY function
  • Oracle VIEW - Convert VARCHAR2 to DATE and find MIN/MAX of DATE
  • In SQL, Main table should match with lookup table for the multiple columns
  • Very slow performance for inner join with restrictive where clause (very small subset of rows)
  • oracle sql 2 columns in one column
  • SQL query to get conflicting values in JSONB from a group
  • Two queries with same tables and join logic but different results
  • How to update a table joined with another table multiple times in Oracle?
  • Crystal Report how can I get data for every 5th of months
  • Finding MAX() from a serial number field which contains characters in SQL Server
  • Getting all distinct values and sorting them by hour
  • Database Compatibility Level
  • Combine WITH statement with UNION
  • Self Nested SQL join
  • My store procedure only executes my first select statement while I wrote three?
  • Inserting Date gives error ORA-01861: literal does not match format string
  • Need to join row data into columns in Oracle
  • Syntax error Missing operator in my case statement
  • Filter the values from the source in merge query in mssql
  • How can i replace/convert number to string
  • Updating a table, on a button press, with selected information
  • Oracle SQL report generation without null
  • Number masking & nls_parameter
  • Delete duplicate roles for SQL server
  • SQL Server: How to change collation of all columns of my select on the fly
  • SQL insert into statement leads to weird (cartesian product) result
  • check constraint sql server
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org