asked
PIVOT on Multiple Columns?
Hi there, Is it possible to PIVOT across multiple columns? CustID ColumnName ColumnLabel ColumnValue 4612 yesnoControl1 KITCHEN false 4612 yesnoControl46 KITCHEN true 4612 yesnoControl1 KITCHEN false 4612 yesnoControl46 KITCHEN true So far I have: SELECT * FROM (SELECT CustID,ColumnLabel,ColumnValue FROM Reports WHERE CustID IN (4612) ) AS y PIVOT (MAX(ColumnValue) FOR ColumnLabel IN (KITCHEN)) as x Which produces: CustID KITCHEN 4612 false But I am looking for a result like: CustID yesnoControl1 KITCHEN yesnoControl46 KITCHEN 4612 false false true true Or a result like: CustID yesnoControl1_KITCHEN yesnoControl46_KITCHEN 4612 false true Is this possible? TIA
@David 2 1 To pivot the data by more than one column is certainly possible, but usually, it is best not to use the PIVOT for it because the latter is limited to only one column. The real question is why do you have duplicate rows in the source data? For example, rows 1 and 3 are duplicates, and rows 2 and 4 are duplicates as well. Please let me know what is the reason to have the data repeated and I can try to come up with the select statement to shape the source as needed.
@Oleg thanks. I'm not in front of the databases right now but I do not believe that the rows are complete duplicates (but I will confirm this tomorrow), I simply cut out any additional columns to make the example as simple as possible to explain. If I remember there are additonal columns like FormID and SubID. If you need these I can amend the question tomorrow. Many thanks.
@David 2 1 I just need to understand what needs to be accomplished. For example, the second option (Or a result like...) in your question can be accomplished via normal PIVOT after the concat on 2 columns is performed, i.e.
select pvt.* from ( select CustID, ColumnName + '_' + ColumnLabel Combined, ColumnValue from Reports -- where CustID in (4612) ) src pivot (max(ColumnValue) for Combined in ( [yesnoControl1_KITCHEN], [yesnoControl146_KITCHEN] ) ) pvt;The first option can be done too, but via CASE statements rather than via PIVOT due to the single column limitation.
@Oleg basically 'yesnoControl1 KITCHEN' and 'yesnoControl46 KITCHEN' are 2 seperate things pulled from the JSON string. Unfortunately the label 'KITCHEN' was reused in the forms so I need to distinguish between the two, because when I ran my PIVOT it grouped the two KITCHEN results together. If you know what I mean. Thanks as always.
18 People are following this question.
