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:

 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?


more ▼

asked Aug 14 at 02:15 PM in Default

avatar image

David 2 1
1.2k 56 61 67

@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.

Aug 14 at 07:50 PM Oleg

@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.

Aug 14 at 07:58 PM David 2 1

@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.

    from (
            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.

Aug 14 at 08:16 PM Oleg

@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.

Aug 14 at 09:02 PM David 2 1

@Oleg once again you are a genius. This works perfectly although I decided to swap around the ColumnLabel and ColumnName to make the columns slightly easier to read in my SSRS report. Thanks as always.

Aug 15 at 09:50 AM David 2 1
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 14 at 02:15 PM

Seen: 30 times

Last Updated: Aug 15 at 09:50 AM

Copyright 2017 Redgate Software. Privacy Policy