Pivot row unpivot multiple columns

Question

I have a multiple join query to get following result.

Color   Size    AQty       BQty        CQty        DQty
A1      L       1           2           3           4
A1      M       1           2           3           4
A1      S       1           2           3           4
A1      XL      1           2           3           4
B1      L       1           2           3           4
B1      M       1           2           3           4
B1      S       1           2           3           4
B1      XL      1           2           3           4
B1      XXL     1           2           3           4
C1      S       1           2           3           4
C1      L       1           2           3           4

but now how to distinct the size field row to column and then AQty, BQty, CQty and DQty column to row? I want to the following result.

Color   Total   L  M    S   XL  XXL
A1      AQty    1  1    1   1   0
A1      BQty    2  2    2   2   0
A1      CQty    3  3    3   3   0
A1      DQty    4  4    4   4   0
B1      AQty    1  1    1   1   1
B1      BQty    2  2    2   2   2
B1      CQty    3  3    3   3   3
B1      DQty    4  4    4   4   4
C1      AQty    1  0    1   0   0
C1      BQty    2  0    2   0   0
C1      CQty    3  0    3   0   0
C1      DQty    4  0    4   0   0

I want it like this question result enter link description here, but my size field is dynamic data. How to distinct size field dynamic data? I don't know how to code it, I tried the following code without success.

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
                 (
                     SELECT DISTINCT 
                            ','+ Size
                     FROM testpovit c FOR XML PATH(''), TYPE
                 ).value('.', 'nvarchar(max)'), 1, 1, '');

SET @query = 'select size = col,color,total,
         '+@cols+'
        from
        (
          select color,total col, value
          from testpovit
          cross apply
          (
            select AQty, cast(AQty as varchar(10)) union all
            select BQty, cast(BQty as varchar(10)) union all
            select CQty, cast(BQty as varchar(10)) union all
            select DQty, cast(BQty as varchar(10)) 
          ) c(col, value)
        ) d
        pivot
        (
          max(value)
          for size in ( '+@cols+')
        ) piv';
print @query
EXECUTE (@query);

This is SQL script

CREATE TABLE test([color] varchar(5), [size] varchar(5),[AQty] varchar(10),[BQty] varchar(10),[CQty] varchar(10),[DQty] varchar(10));

INSERT INTO test
    ([color], [size], [AQty], [BQty],[CQty],[DQty])
VALUES
    ('A1', 'L','1','2','3','4'),
    ('A1', 'M','1','2','3','4'),
    ('A1', 'S','1','2','3','4'),
    ('A1', 'XL','1','2','3','4'),
    ('B1', 'L','1','2','3','4'),
    ('B1', 'M','1','2','3','4'),
    ('B1', 'S','1','2','3','4'),
    ('B1', 'XL','1','2','3','4'),
    ('B1', 'XXL','1','2','3','4'),
    ('C1', 'L','1','2','3','4'),
    ('C1', 'S','1','2','3','4');

You will need to use Dynamic SQL. [sp_executesql](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15) or Search in this site for dynamic pivot. There are lots of questions on this — Squirrel, May 07 '21 at 02:12

score 0 · Accepted Answer · answered May 07 '21 at 02:55

0

you can do it like this:

CREATE TABLE #temp([color] varchar(5), [size] varchar(5),[AQty] varchar(10),[BQty] varchar(10),[CQty] varchar(10),[DQty] varchar(10));

INSERT INTO #temp([color], [size], [AQty], [BQty],[CQty],[DQty])
VALUES   ('A1', 'L','1','2','3','4')
       , ('A1', 'M','1','2','3','4')
       , ('A1', 'S','1','2','3','4')
       , ('A1', 'XL','1','2','3','4')
       , ('B1', 'L','1','2','3','4')
       , ('B1', 'M','1','2','3','4')
       , ('B1', 'S','1','2','3','4')
       , ('B1', 'XL','1','2','3','4')
       , ('B1', 'XXL','1','2','3','4')
       , ('C1', 'L','1','2','3','4')
       , ('C1', 'S','1','2','3','4')

declare @cols as varchar(max), @sql varchar(max)
select @cols = STRING_AGG(col, ', ') from (
    select distinct QUOTENAME(size) as col from #temp
) as t

set @sql = '
select color, col as total, ' + @cols + '
from(
    select color, size, col, [value]
    from #temp
    cross apply (
    select ''AQty'', cast(AQty as varchar(10)) union all
    select ''BQty'', cast(BQty as varchar(10)) union all
    select ''CQty'', cast(CQty as varchar(10)) union all
    select ''DQty'', cast(DQty as varchar(10)) 
    ) c(col, [value])
) d
pivot
(
  max(value)
  for size in (' + @cols + ')
) piv
order by color, total
'
exec(@sql)

answered May 07 '21 at 02:55

Brucelin Michael

475
4
10

thank you,but now my datasource is a multiple join query ,how to replace #temp to my multiple join query sql – beiduoan May 07 '21 at 03:25
I am not very clear about your usage scenario, if you can use stored procedure, just insert your multiple join query result into #temp, then use #temp. – Brucelin Michael May 07 '21 at 03:32
Thank you very much, your solution is great – beiduoan May 07 '21 at 06:51
I'm sorry, I didn't understand what you mean. – Brucelin Michael May 07 '21 at 09:45
i change this sql code select chinese ,but not display garbled ??,i hope display chinese field,i how to coding display chinese string cross apply ( select N''总数1'', cast(AQty as varchar(10)) union all select N''总数2'', cast(BQty as varchar(10)) union all select N''总数3'', cast(CQty as varchar(10)) union all select N''总数4'', cast(DQty as varchar(10)) – beiduoan May 07 '21 at 09:50

Pivot row unpivot multiple columns

1 Answers1