How do I combine two columns into single column in static / dynamic pivot query using SQL Server

Question

I have a schema like this

demo(month_year(navarchar), datecount(int), destination(nvarchar), type(nvarchar)).

In output i want to transform the rows to columns with concatenate columns.

datecount  |  Month_year   | destination   type  
-------------------------+---------------+-------------------
07         | March - 18    | ABC          No
23         | August - 2018 | ABC          No
29         | August - 2018 | XYZ          Sold Out 
04         | July - 2018   | PQR          Sold Out
10         | July - 2018   | XYZ          No
25         | July - 2018   | ABC          Sold Out

In the output i want,

Month_Year      1                 2                 3 ...     
July - 2018     04(Sold Out-PQR)  10(No-XYZ)        25(Sold Out-ABC)
August - 2018   23(No-ABC)        29(Sold Out-XYZ)

I have tried more using the PIVOTE function. Facing the problem to display combination of datecount with destination and type as per above expected output. But could not get the solution. Please help me.

The column number depends on the dates of each month. Usually i want static query for the output, — Pankaj Sonawane, Jul 09 '18 at 07:11
Seems like a similar problem to what I recently had: https://stackoverflow.com/questions/51166622/dynamic-conversion-rows-to-columns-sql — SQL_M, Jul 09 '18 at 07:21
@SQL_M : looking for combine 3 columns and show output in single column. — Pankaj Sonawane, Jul 09 '18 at 07:25

score 1 · Accepted Answer · answered Jul 09 '18 at 07:26

Just concatenate the values before pivoting.

;WITH ToPivot AS
(
    SELECT
        D.month_year,
        ConcatenatedValues = CONVERT(VARCHAR(10), D.datecount) + '(' + D.type + '-' + D.destination + ')',
        NumberToPivot = ROW_NUMBER() OVER (PARTITION BY D.month_year ORDER BY D.datecount ASC)
    FROM
        Demo AS D
)
SELECT
    P.*
FROM
    ToPivot AS T
    PIVOT (
        MAX(T.ConcatenatedValues)
        FOR T.NumberToPivot IN (
            [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], 
            [11], [12], [13], [14], [15], [16], [17], [18], [19], 
            [20], [21], [22], [23], [24], [25], [26], [27], [28], 
            [29], [30], [31])
    ) AS P

Superbb..!!! Exactly why I posted this. I could not have achieved this myself. Thanks a lot @EzLo, this indeed works! — Pankaj Sonawane, Jul 09 '18 at 07:36

How do I combine two columns into single column in static / dynamic pivot query using SQL Server

1 Answers1