Rows to Columns SQL

Question

Hi I am working on a query to change this format:

to this:

I have been working with this code but not sure if I am following the correct way:

select [Duplicate Stores],[Missing Products],channel,Date
from (SELECT
    CheckType,
    Channel,
    Date,
    AttributeValue
FROM MissingList 
 ) d
pivot (
 max(AttributeValue)
 for CheckType in ([Duplicate Stores], [Missing Products])
) piv;

Answer 1

You can use STUFF and FOR XML to get your comma-separated attribute values like this:

SELECT
    [Duplicate Stores]
    ,[Missing Products]
    ,channel
    ,[Date]
FROM (
         SELECT
             m2.CheckType
             ,m2.Channel
             ,m2.[Date]
             ,Attribute = STUFF(
                          (
                              SELECT ',' + CAST(m1.Attribute AS VARCHAR)
                              FROM MissingList m1
                              WHERE m2.CheckType = m1.CheckType
                                  AND m2.Channel = m1.Channel
                                  AND m2.[Date] = m1.[Date] 
                              FOR XML PATH('')
                          )    , 1, 1, ''
                               )
         FROM MissingList m2
         GROUP BY m2.CheckType
             ,m2.Channel
             ,m2.[Date]
     ) d
PIVOT (   MAX(Attribute)
          FOR CheckType IN ([Duplicate Stores], [Missing Products])
      ) piv;

EDIT: Here's the sample data I used, based on the OP's question:

CREATE TABLE #Missinglist (CheckType VARCHAR(100), Channel VARCHAR(10), [Date] DATE, Attribute INT)

INSERT INTO #Missinglist (CheckType, Channel, Date, Attribute)
VALUES ('Duplicate Stores','WMT','12/20/2017',4055),
('Duplicate Stores','WMT','12/20/2017',6807),
('Duplicate Stores','WMT','12/20/2017',7020),
('Missing Products','WMT','12/20/2017',3484046),
('Missing Products','WMT','12/20/2017',3219002),
('Missing Products','WMT','12/20/2017',5875045),
('Duplicate Stores','BB','1/1/2017',243424),
('Duplicate Stores','BB','1/1/2017',24234),
('Duplicate Stores','BB','1/1/2017',66767),
('Missing Products','BB','1/1/2017',8895),
('Missing Products','BB','1/1/2017',236),
('Missing Products','BB','1/1/2017',7356),
('Duplicate Stores','BB','1/2/2017',234),
('Duplicate Stores','BB','1/2/2017',75237),
('Duplicate Stores','BB','1/2/2017',232),
('Missing Products','BB','1/2/2017',883),
('Missing Products','BB','1/2/2017',236),
('Missing Products','BB','1/2/2017',7356)

Using my query yields the following result set:

Duplicate Stores    Missing Products        channel Date
--------------------------------------------------------------
243424,24234,66767  8895,236,7356           BB      2017-01-01
234,75237,232       883,236,7356            BB      2017-01-02
4055,6807,7020      3484046,3219002,5875045 WMT     2017-12-20

Answer 2

try this:

with #all as 
(select * from YourTable)
 select * from (select Date, Channel,
,case when Checktype ='Duplicated Stores' then 'Duplicated Stores'
    when Checktype ='Missing Products' then 'Missing Products'
    end as seqno
from #all
) as datatable
pivot(
Max([ATTRIBUTE]) 
for [seqno] in ([Duplicated Stores],[Missing Products])
)as piv

This however will not group each attribute and have them in commas like you want them to be. Instead it will give you each attribute individually.