How to convert column header and its value to string in sql?

Question

I have a table with columns: col1, col2, col3 . The table has many rows.

Suppose that val1, val2, val3 is one such string. I want to get the result as

 Col1, Val1 Col2, Val2 Col3, Val3 

These are three rows — one for each column and its value.

I am using SQL Server 2008. I read about pivots. Are there reasons to solve this problem? Can someone direct me to some examples or solutions how to solve this problem?

thanks a lot

Answer 1

Maybe something like this:

Test Data

 DECLARE @T TABLE(Col1 INT, Col2 INT, Col3 INT) INSERT INTO @T VALUES (1,1,1) 

Query

 SELECT * FROM ( SELECT t.Col1, t.Col2, t.Col3 FROM @T AS t ) AS SourceTable UNPIVOT ( Value FOR Col IN (Col1,Col2,Col3) ) AS unpvt 

Exit

 1 Col1 1 Col2 1 Col3 

Answer 2

To do this, read the following: Using PIVOT and UNPIVOT

The rotation function allows you to convert row values ​​from a column.

Also check: Dynamic Help in SQL Server

Example:

 create table #temptable(colorname varchar(25),Hexa varchar(7),rgb varchar(1), rgbvalue tinyint) GO insert into #temptable values('Violet','#8B00FF','r',139); insert into #temptable values('Violet','#8B00FF','g',0); insert into #temptable values('Violet','#8B00FF','b',255); insert into #temptable values('Indigo','#4B0082','r',75); insert into #temptable values('Indigo','#4B0082','g',0); insert into #temptable values('Indigo','#4B0082','b',130); insert into #temptable values('Blue','#0000FF','r',0); insert into #temptable values('Blue','#0000FF','g',0); insert into #temptable values('Blue','#0000FF','b',255); SELECT colorname,hexa,[r], [g], [b] FROM (SELECT colorname,hexa,rgb,rgbvalue FROM #temptable) AS TableToBePivoted PIVOT ( sum(rgbvalue) FOR rgb IN ([r], [g], [b]) ) AS PivotedTable; 

Answer 3

Create a tempary table:

 CREATE TABLE #table2 ( name NCHAR, bonus INT ) 

Now select and follow the instructions below if there is one empty.

 SELECT * FROM #table2 INSERT INTO #table2 (name,bonus) VALUES ('A',10) INSERT INTO #table2 (name,bonus) VALUES ('B',20) INSERT INTO #table2 (name,bonus) VALUES ('C',30) 

After inserting the values ​​into the table. select and run the following line if you get entries:

 SELECT * FROM #table2 

Input:

 name bonus A 10 B 20 C 30 

Change the input like this result

Result:

 Cost ABC Bonus 10 20 30 

Using this code:

 SELECT 'Bonus' AS Cost, [A],[B],[C] FROM (SELECT name, Bonus FROM #table2) AS TempTbl PIVOT ( AVG(bonus) FOR [name] IN ([A],[B],[C]) ) AS PivotTable;