CODEWITHSUNDEEP
sql-servert-sqlssms
Giuseppe Lolli
Giuseppe Lolli

Reputation: 177

Pivot Table With multiple Values for a number of x columns

i have a problem with pivot table in SQL Server:

I have a table with the next information (i dont know how much different values could have column1):

Column1   Value
---------------
PRODUCT_4   1
PRODUCT_4   2
PRODUCT_4   3
PRODUCT_6   10
PRODUCT_6   20
PRODUCT_6   30
PRODUCT_8   100
PRODUCT_8   200
PRODUCT_8   300
...         ...
PRODUCT_X   1
PRODUCT_X   2
PRODUCT_X   3

I want to transform this into a pivot table to get the next output:

product_4    product_6    product_8    ...    product_x
1            10           100                1
2            20           200                2
3            30           300                3

I was using the next query:

DECLARE 
@cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(column1) 
                    from dbo.Context_Table
                   group by column1
           FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
print @cols

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, Column1
                from dbo.Context_Table
            ) x
            pivot 
            (
                max(value)
                for Column1 in (' + @cols + N')
            ) p '

exec sp_executesql @query;

But the ouput is not that i expected...

PRODUCT_4   PRODUCT_6   PRODUCT_8   PRODUCT_X
3           30          300         3

My question is... how can i get all the values without using the aggregate function MAX?

Thanks all!

Upvotes: 0

Views: 109

Answers (3)

shantanu singh
shantanu singh

Reputation: 1

Try this/ Or something along these lines.

select PRODUCT_4,PRODUCT_6,PRODUCT_8
from 
(select name,value,row_number() over (partition by name order by value) as id from product) p
pivot
(
    max(value)
    for name
    IN( PRODUCT_4,PRODUCT_6,PRODUCT_8)
)
as a

Hope this helps. Thanks.

Upvotes: 0

hkravitz
hkravitz

Reputation: 1385

add ROW_NUMBER() to your dynamic pivot:

 set @query = N'SELECT  ' + @cols + N' from 
         (
            select ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY value) RN , value, Column1
            from ##T
        ) x
        pivot 
        (
            max([value])
            for Column1 in (' + @cols + N')
        ) p '

 exec sp_executesql @query;

Upvotes: 2

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You must add a changing column to your query, I do this with ROW_NUMBER():

Just try it out:

DECLARE @tbl TABLE(Column1 VARCHAR(100),[Value] INT)
INSERT INTO @tbl VALUES
 ('PRODUCT_4',1)
,('PRODUCT_4',2)
,('PRODUCT_4',3)
,('PRODUCT_6',10)
,('PRODUCT_6',20)
,('PRODUCT_6',30)
,('PRODUCT_8',100)
,('PRODUCT_8',200)
,('PRODUCT_8',300)
,('PRODUCT_X',1)
,('PRODUCT_X',2)
,('PRODUCT_X',3)

SELECT p.*
FROM
(
    SELECT ROW_NUMBER() OVER(PARTITION BY Column1 ORDER BY [Value]) AS ValueInx
          ,*
    FROM @tbl AS t
) AS tbl
PIVOT
(
    MAX([Value]) FOR Column1 IN(PRODUCT_4,PRODUCT_6,PRODUCT_8,PRODUCT_X)
) AS p:

The result

+----------+-----------+-----------+-----------+-----------+
| ValueInx | PRODUCT_4 | PRODUCT_6 | PRODUCT_8 | PRODUCT_X |
+----------+-----------+-----------+-----------+-----------+
| 1        | 1         | 10        | 100       | 1         |
+----------+-----------+-----------+-----------+-----------+
| 2        | 2         | 20        | 200       | 2         |
+----------+-----------+-----------+-----------+-----------+
| 3        | 3         | 30        | 300       | 3         |
+----------+-----------+-----------+-----------+-----------+

You will easily integrate this into your dyanmic approach...

Upvotes: 2

Related Questions