SQL query unknown rows into columns

Question

I asked this question and it was marked as a duplicate of How to pivot unknown number of columns & no aggregate in SQL Server?, but that answer doesn't help me.

I have a table of data that looks like this, with an unknown number of rows and values.

RecID     Name      Value
1         Color     Red
2         Size      Small
3         Weight    20lbs
4         Shape     Square

I need a query that will return the data like this, building out a column for each row. I cannot hard code anything except the column headers 'Name' and 'Value'.

Color     Size     Weight     Shape
Red       Small    20lbs      Square

Here is what I have so far that is partly working:

INSERT INTO @Table VALUES
(1,'Color' ,'Red'),
(2,'Size'  ,'Small'),
(3,'Weight','20lbs'),
(4,'Shape' ,'Square')

 ;with mycte
 as
 (
SELECT rn,cols,val
FROM   (SELECT  row_number() over(order by Name) rn, Name, Value
        FROM  @Table) AS src1
UNPIVOT (val FOR cols
IN ( [Name], [Value])) AS unpvt
 )

SELECT *
FROM   (SELECT rn,cols,val
        FROM   mycte) AS src2 PIVOT
( Max(val) FOR rn IN ([1], [2], [3])) AS pvt

Which returns:

cols    1   2   3
Name    Color   Shape   Size
Value   Red Square  Small

Two problems with this that I can't seem to resolve.

I don't need the column headers and the first column that has cols, Name, Value in it.
Can't figure out how to have it build a column for each row without specifying the [x] identifiers.

Any guidance would be great I've been stuck on this a while now.

dean · Accepted Answer · 2014-04-14T13:05:31.650

2

declare @collist nvarchar(max)
SET @collist = stuff((select distinct ',' + QUOTENAME(name) 
            FROM #t -- your table here
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

declare @q nvarchar(max)
set @q = '
select * 
from (
    select rn, name, Value
        from (
        select *, row_number() over (partition by name order by RecID desc) as rn
        from #t -- your table here
    ) as x
) as source
pivot (
    max(Value)
    for name in (' + @collist + ')
) as pvt
'

exec (@q)

edited Apr 14 '14 at 13:05

answered Apr 14 '14 at 12:54

dean

9,960
2
25
26

This works perfectly! Thank you so much I was way off. – Blaze Apr 14 '14 at 13:40

AK47 · Answer 2 · 2014-04-14T12:58:31.207

Until now, I have reached to following code, hope it helps you,

Current outpu comes as

Color   Shape   Size    Weight
Red     NULL    NULL    NULL
NULL    NULL    Small   NULL
NULL    NULL    NULL    20lbs
NULL    Square  NULL    NULL

Create table DyTable  
(
    tid int,
    Name varchar(20),
    Value varchar(20)
)

INSERT INTO DyTable VALUES
(1,'Color' ,'Red'),
(2,'Size'  ,'Small'),
(3,'Weight','20lbs'),
(4,'Shape' ,'Square')

DECLARE @Cols NVARCHAR(MAX);
DECLARE @Cols1 NVARCHAR(MAX);

SELECT @Cols =  STUFF((
                    SELECT DISTINCT ', ' +  QUOTENAME(Name) 
                    FROM DyTable
                    FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,'')
                ,@Cols1 =  STUFF((
                    SELECT DISTINCT ', max(' +  QUOTENAME(Name) + ') as ' + Name
                    FROM DyTable
                    FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,'')


DECLARE @Sql NVARCHAR(MAX)
Select @Cols

SET @Sql  = 'Select '+ @Cols1 +'
            from (SELECT ' + @Cols +   '
            FROM DyTable t
            PIVOT (MAX(Value)
                    FOR Name 
                    IN (' + @Cols + ')
                    )P)a'

EXECUTE sp_executesql @Sql

I would suggest that you still use the windowing function like `row_number()` so you can still return multiple rows for each name. If you don't then you will only return one row of each. — Taryn, Apr 14 '14 at 12:54
@bluefeet, I didn't understand what you are saying, but have updated my ans. This might not be the best way to do it, but it works. Please let me know if we can make more correct & short method. — AK47, Apr 14 '14 at 12:59
Take a look at this answer here - http://stackoverflow.com/questions/22968734/find-t-sql-to-transpose-a-table-by-chunks/22969104#22969104 -- the problem is that when you aggregate a string you will return only one row of data. Applying a windowing function like row_number will allow for multiple rows of Name, etc to be returned. — Taryn, Apr 14 '14 at 13:02

SQL query unknown rows into columns

2 Answers2

Linked