Dynamic Pivot multiple columns in SQL Server

Question

I have a table like this

Id   Name   FromAddress   ToAddress
1    Joey      ABC          JKL
2    Joey      DEF          MNP
3    Joey      GHI          OQR

I am looking for the following output with dynamic pivot in SQL Server

Name   FromAdrs1   FromAdrs2   FromAdrs3   ToAdrs1   ToAdrs2   ToAdrs3
Joey     ABC         DEF         GHI          JKL      MNP       OQR

Note: The number of rows changes based on the Id value, so I am trying to get the output using dynamic Pivot.

Here is the code that I tried, it's looks like it is correct, but throwing me an error.

IF OBJECT_ID('temp..#temp') IS NOT NULL
DROP TABLE #temp

CREATE TABLE #temp
(
    Id INT, Name VARCHAR(10), FromAddress VARCHAR(10), ToAddress VARCHAR(10)
)

INSERT INTO #temp VALUES (1, 'Joey', 'ABC', 'JKL'), (2, 'Joey', 'DEF', 'MNP'), (3, 'Joey', 'GHI', 'OQR')

--SELECT * FROM #temp


DECLARE @colsFrom AS NVARCHAR(MAX),
@colsTo AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);

SET @colsFrom = STUFF((SELECT distinct ',' + QUOTENAME(CONVERT(VARCHAR(2), t.id) + 'From') 
        FROM #temp t
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

SET @colsTo = STUFF((SELECT distinct ',' + QUOTENAME(CONVERT(VARCHAR(2), t.id) + 'To') 
        FROM #temp t
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT *, ' + @colsFrom + ', ' + @colsTo + ' from 
        (
            select *
            from #temp
       ) T
        PIVOT 
        (
            max(FromAddress)
            for Id in (REPLACE('''+@colsFrom+''',''From'',''''))
        ) p
        PIVOT 
        (
            max(ToAddress)
            for Id in (REPLACE('''+@colsTo+''',''To'',''''))
        ) Q'

execute(@query)

DROP TABLE #temp

Any help is appreciated. Thank you all for your time.

Edit: This is the error

Why would you do this? This is not how relational databases i.e. SQL works. If you need the format in another language it should be easier to transform the format in that language. — Benedikt S. Vogler
– Benedikt S. Vogler, Commented Jul 31, 2017 at 17:31
@Isaiah3015 Added the error, it's says there is a parenthesis missing, but I can't figure out where. — MycrowSoft
– MycrowSoft, Commented Jul 31, 2017 at 17:33
@BenediktS.Vogler It's a dynamic PIVOT. They suck but they basically work. — Bacon Bits
– Bacon Bits, Commented Jul 31, 2017 at 17:35
@BenediktS.Vogler Sorry, I didn't get you. What do you mean by format in another language? All I am trying to do is dynamically pivot multiple columns (in this case 2) in a table (or query). — MycrowSoft
– MycrowSoft, Commented Jul 31, 2017 at 17:36

Bacon Bits · Accepted Answer · 2017-07-31 17:50:56Z

I believe the problem is the IN () expression in the PIVOTs. The column list explicitly has to be a list of fields names, not a function and not a list of varchar literals or function values. You've got a REPLACE() function in there. The engine expects to be looking for a field named [REPLACE] and then gets confused by the open parentheses that shows up.

This is valid (square brackets for emphasis):

SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      ([Emp1], [Emp2], [Emp3], [Emp4], [Emp5])
)AS unpvt;

This is not:

SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      ('Emp1', 'Emp2', 'Emp3', 'Emp4', 'Emp5')
)AS unpvt;

And this is not valid:

SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (REPLACE('Emp1','1','A'), REPLACE('Emp2','2','B'))
)AS unpvt;

Replace the execute(@query) with a select @query or print @query to see the query your code generated and troubleshoot the syntax in a query analyzer that way. Then work backwards.

You want to do the REPLACE() at the same level you're building the query. The query that ends up in the @query variable should already have the column names fixed.

Alternately, you could generate @colsFromLabels, @colsToLabels, @colsFrom and @colsTo with the former two have the 'from' and to bits added and the latter two just being column names.

Your desired output is a little gross as far as square bracket escaping, too.

Thank you for your response. I will try generating Label columns as you suggested and use them in output. I will reply back, if it works.
No, it's not working. When I pass the same Id column ([1],[2],[3]) for two Pivot statements, it's throwing the following error. "The column name "1" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument"
Oh, maybe that's right. Maybe you need Oracle to aggregate the same column twice. Well, you could put them each into a CTE and then join them together for the output.

Ryan B. · Accepted Answer · 2017-07-31 18:21:01Z

ok, I created a temp table to do some testing on. The solution requires an unpivot first. I recommend running with/without the extra test data to get a sense of some other behaviors that surround this solution -- the weirdness that comes with the MAX aggregation and lack of new rows that you might have expected when changing the value in 'name'.

GL. Hope it helps.

-------------------------
-- Some test data here
CREATE table #addresses ( Id int, Name varchar(5), FromAddress varchar(5), ToAddress varchar(5))
insert into #addresses(id, Name, FromAddress, ToAddress) Values
(1,'Joey', 'ABC', 'JKL')
, (2,'Joey', 'DEF', 'MNO')
, (3,'Joey', 'GHI', 'PQR')
, (4,'Spike', 'XXX', 'YYY')
, (1,'Spike', 'QQQ', 'RRR')

-------------------------
--  Solution starts here.  create a temp table and unpivot your data into it.
--  Your initial technique of does not work, PIVOT only supports one aggregation

CREATE table #unpvt(RowColCode varchar(20), vals varchar(20))
Insert into #unpvt
SELECT ColCode + '_' + Cast(ID as varchar(2)) as RowColCode, vals
FROM #addresses a
UNPIVOT
    (vals for ColCode in (Name,FromAddress,ToAddress)) c

-------------------------
--  Read the temp table for a column list

declare @ColList nvarchar(max)
set @ColList = STUFF((
    SELECT distinct ',[' + t.RowColCode + ']'
            FROM #unpvt t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')


-------------------------
-- 're pivot' the data using your new column list

declare @qry varchar(max)
set @qry = '

select *
from
    #unpvt
    PIVOT(
    MAX(vals)
    FOR RowColCode in (' +@ColList +  ') 
    ) rslt
'


execute(@qry)

Collectives™ on Stack Overflow

Dynamic Pivot multiple columns in SQL Server

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related