How to convert multiple columns into a single row with row number?

Question

I'm trying to figure out a way to convert [Column1],[Column2],[Column3],[Column4],[Column5] into a single column [Type]. I also want it to have row number. So an example of how I would want it to look like. ALso, what if I just want column with data to output? Like Column1 and Column2 both have values but column3, 4, and 5 don't. In this case, I just want 1 and 2 to show on my resultset.

Now:

[ID1]-[Column1],[Column2],[Column3],[Column4],[Column5]

[ID2]-[Column1],[Column2],[Column3],[Column4],[Column5]

Desire:

[ID1]-[Column1],[Row1]

[ID1]-[Column2],[Row2]

[ID1]-[Column3],[Row3]

[ID1]-[Column4],[Row4]

[ID1]-[Column5],[Row5]

[ID2]-[Column1],[Row1]

[ID2]-[Column2],[Row2]

Etc......

Thank you!

Possible duplicate of [How to insert row into a table by select column name and value from another table in SQL Server](http://stackoverflow.com/questions/39763621/how-to-insert-row-into-a-table-by-select-column-name-and-value-from-another-tabl) — Sandip - Frontend Developer, Sep 29 '16 at 14:17
dont understand the RowNumber? Is just to indicate the column number? — Juan Carlos Oropeza, Sep 29 '16 at 14:36

Jaydip Jadhav · Answer 1 · 2016-09-29T14:37:42.740

0

Try some thing like this but this is may not reliable solution but get work done first

SELECT Col,Row_NUMBER() OVER(ORDER BY [ID]) RowNo 
FROM (

         SELECT [ID],CAST([ID] AS VARCHAR(30))+'-'+[COLUMN1] as Col
         FROM TableName
         UNION ALL
         SELECT [ID],CAST([ID] AS VARCHAR(30))+'-'+[COLUMN2] as Col
         FROM TableName   
         UNION ALL
         SELECT [ID],CAST([ID] AS VARCHAR(30))+'-'+[COLUMN3] as Col
         FROM TableName
         ...Like wise for other columns

)M
ORDER BY [ID]

edited Sep 29 '16 at 14:37

answered Sep 29 '16 at 14:24

Jaydip Jadhav

12,179
6
24
40

Can you provide some explanation of why your answer would answer the question? Code-only answers are not as useful to the asker as a well explained answer. – Zack Sep 29 '16 at 14:28

score 0 · Answer 2 · answered Sep 29 '16 at 14:45

one way to unpivot a table is by using Cross/Outer apply and Table Value Constructors.

--sample data
WITH cte AS (
    SELECT  *
    FROM    (VALUES (1, 'col1', 'col2', 'col3', 'col4', 'col5'),
                    (2, 'col1', 'col2', 'col3', 'col4', 'col5')) 
    t(id, column1,column2,column3,column4,column5) 
)
--query
SELECT  cte.id, 
        t.* 
FROM    cte 
OUTER APPLY (VALUES(column1, 1),(column2, 2),(column3, 3),(column4, 4),(column5, 5)) t([type],[rownum])

gofr1 · Answer 3 · 2016-09-29T15:49:52.893

0

You can use UNPIVOT to reverse columns to rows:

;WITH YourTable AS (
SELECT *
FROM (VALUES
('ID1','Column11','Column12','Column13','Column14','Column15'),
('ID2','Column21','Column22','Column23','Column24','Column25')
) as t([ID],[Column1],[Column2],[Column3],[Column4],[Column5])
)

SELECT *
FROM (
    SELECT  [Column1] as [Row1],
            [Column2] as [Row2],
            [Column3] as [Row3],
            [Column4] as [Row4],
            [Column5] as [Row5],
            [ID]
    FROM YourTable
) t
UNPIVOT (
    [Type] FOR [Column] IN ([Row1],[Row2],[Row3],[Row4],[Row5])
) as unp

Output:

ID  Type        Column
ID1 Column11    Row1
ID1 Column12    Row2
ID1 Column13    Row3
ID1 Column14    Row4
ID1 Column15    Row5
ID2 Column21    Row1
ID2 Column22    Row2
ID2 Column23    Row3
ID2 Column24    Row4
ID2 Column25    Row5

edited Sep 29 '16 at 15:49

answered Sep 29 '16 at 15:41

gofr1

15,741
11
42
52

What if I just want column with data to output? Like Column11 and Column12 both have values but column13, 14, and 15 don't. In this case, I just want 11 and 12 to show on my resultset. – EkansDasnakE Sep 29 '16 at 18:01
You can try set NULLs instead of this values and try to run my script, AFAIK unpivot will get rid of this rows. – gofr1 Sep 29 '16 at 18:03
Will remove NULL values, tested here https://data.stackexchange.com/stackoverflow/query/edit/546616 – gofr1 Sep 29 '16 at 18:12

How to convert multiple columns into a single row with row number?

3 Answers3