Subquery for PIvot table in sql

Question

I have written a query to generate a Pivot. The code is running absolutely fine. Instead of hard coding the value("1","2","3"), IS it possible to write a subquery?

SELECT *
FROM (
    SELECT * from #Registered2
) as s
PIVOT
(
    SUM(s.Registered_customer_Count)
    FOR [Offer_cover_id] IN ("1","2","3")
)AS pvt

Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Mani Deep, Jul 10 '18 at 10:07

Chanukya · Accepted Answer · 2018-07-10T10:27:17.717

0

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Offer_cover_id]) 
            FROM #Registered2 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT  ' + @cols + ' from 
            (
                select*
                from Registered2 
           ) x
            pivot 
            (
                 SUM(Registered_customer_Count)
                for [Offer_cover_id] in (' + @cols + ')
            ) p '


execute(@query)

edited Jul 10 '18 at 10:27

answered Jul 10 '18 at 10:10

Chanukya

5,833
1
22
36

1

This is working perfect but the output is repeating the columns 1,2,3 twice. – Alankar Gupta Jul 10 '18 at 10:18

Subquery for PIvot table in sql

1 Answers1