Tags down


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 :

    [Param01] INT
   ,[Param02] INT
   ,[Param03] INT

    [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');


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)')
                            + '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

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
    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]

#>    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