bring column name into row in sql server

Question

I have a table with column column_name_1 , column_name_2 , column_name_3 and with one row values 0,0,1

column_name_1 , column_name_2  , column_name_3
--------------,----------------,---------------
      0       ,       0        ,      1

I need output like

column_name_1  | 0  
column_name_2  | 0  
column_name_3  | 1

Is it possible?

I have checked for some unpivot example, thats not exactly my case.

Because I need Column name into column value and one row into column.

1.Unpivot with column name

Name, Maths, Science, English
Tilak, 90, 40, 60
Raj, 30, 20, 10

changed into

Name, Subject, Marks
Tilak, Maths, 90
Tilak, Science, 40
Tilak, English, 60

Clearly there is a view, Name column remains its position as it is.

2.SQL Query for generating matrix like output querying related table in SQL Server

Above link also have Customer Name column which is remains same as it is.

But in my case input and output, both not have any same position.

So if still it can be achievable through pivot, Pls help with the code.

please, check this link: https://stackoverflow.com/questions/5216701/sql-query-for-generating-matrix-like-output-querying-related-table-in-sql-server — Alberto Cláudio Mandlate, Feb 12 '19 at 13:23
Try to take look at unpivot : https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017 https://stackoverflow.com/questions/19055902/unpivot-with-column-name — Sanpas, Feb 12 '19 at 13:23
I don't know how it is marked as duplicate. really I am not able to understand and relate with given links, also I can't able to delete this question as I accepted the answer below(It worked for me). Also I am not able to ask new question. Pls. anybody able to help me to release ban. what should I do? — Santhana, Feb 14 '19 at 06:13
@pascalsanchez updated my question can you help to release the question ban. As it was marked as duplicate, not able to ask new question — Santhana, Feb 18 '19 at 09:01

John Cappelletti · Accepted Answer · 2019-02-12T13:31:22.173

2

Clearly UNPIVOT would be more performant, but if you need a dynamic approach without actually using dynamic SQL

Example

Select C.*
 From YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Item  = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('Columns','ToExclude')
             ) C

Returns

Item            Value
Column_name_1   0
column_name_2   0
column_name_3   1

edited Feb 12 '19 at 13:31

answered Feb 12 '19 at 13:26

John Cappelletti

79,615
7
44
66

Awesome! #Respect – Daniel Brughera Feb 12 '19 at 13:31
@DanielBrughera Thanks for the grin :) – John Cappelletti Feb 12 '19 at 13:31

score 1 · Answer 2 · answered Feb 12 '19 at 13:26

1

You want APPLY :

SELECT tt.cols, tt.colsvalue
FROM table t CROSS APPLY
     ( VALUES ([column_name_1], 'column_name_1'), 
              ([column_name_2], 'column_name_2'), 
              ([column_name_3], 'column_name_3') 
     ) tt (colsvalue, cols);

answered Feb 12 '19 at 13:26

Yogesh Sharma

49,870
5
26
52

bring column name into row in sql server

2 Answers2