August 1, 2016 at 12:12 am
Good morning! I am new to pivoting data and trying to figure out if it is possible to pivot our questionnaires table by client and date:
From this:
| Date | Client | QType | Answer |
===========================
| 11.01.16 | 1 | Q1 | 100 |
| 11.01.16 | 1 | Q2 | 150 |
| 11.01.16 | 1 | Q3 | 75 |
| 11.01.16 | 2 | Q1 | 250 |
| 11.01.16 | 2 | Q2 | 100 |
| 11.01.16 | 2 | Q3 | 100 |
| 12.01.16 | 1 | Q1 | 75 |
| 12.01.16 | 1 | Q2 | 50 |
| 12.01.16 | 1 | Q3 | 75 |
| 12.01.16 | 2 | Q1 | 10 |
| 12.01.16 | 2 | Q2 | 50 |
| 12.01.16 | 2 | Q3 | 100 |
Into This:
| Client | Date | Q1 | Q2 | Q3 |
===============================
| 1 | 11.01.16 | 100 | 50 | 75 |
| 2 | 11.01.16 | 250 | 100 | 100 |
| 1 | 12.01.16 | 75 | 50 | 75 |
| 2 | 12.01.16 | 10 | 50 | 100 |
I am hoping that someone out there can help a tired non-pivoting DBA in need!
My apologies if the solution to this is blindingly obvious, I have been working all night and this one escapes me!!!!
Many thanks for any help!
August 1, 2016 at 12:35 am
Quick suggestion
😎
USE TEEST;
GO
SET NOCOUNT ON;
--
;WITH SAMPLE_DATA(Date,Client,QType,Answer) AS
(SELECT Date,Client,QType,Answer
FROM (VALUES
('11.01.16',1,'Q1',100)
,('11.01.16',1,'Q2',150)
,('11.01.16',1,'Q3',75 )
,('11.01.16',2,'Q1',250)
,('11.01.16',2,'Q2',100)
,('11.01.16',2,'Q3',100)
,('12.01.16',1,'Q1',75 )
,('12.01.16',1,'Q2',50 )
,('12.01.16',1,'Q3',75 )
,('12.01.16',2,'Q1',10 )
,('12.01.16',2,'Q2',50 )
,('12.01.16',2,'Q3',100)
) AS X(Date,Client,QType,Answer)
)
SELECT
SD.Date
,SD.Client
,MAX(CASE WHEN SD.QType = 'Q1' THEN SD.Answer END) AS Q1
,MAX(CASE WHEN SD.QType = 'Q2' THEN SD.Answer END) AS Q2
,MAX(CASE WHEN SD.QType = 'Q3' THEN SD.Answer END) AS Q3
FROM SAMPLE_DATA SD
GROUP BY SD.Date
,SD.Client;
Output
Date Client Q1 Q2 Q3
-------- ----------- ----------- ----------- -----------
11.01.16 1 100 150 75
12.01.16 1 75 50 75
11.01.16 2 250 100 100
12.01.16 2 10 50 100
August 1, 2016 at 3:05 am
Thank you so much, worked perfectly!
August 1, 2016 at 3:43 am
August 2, 2016 at 12:23 pm
Eirikur Eiriksson (8/1/2016)
slr010877 (8/1/2016)
Thank you so much, worked perfectly!You are very welcome
😎
For further information on the subject, I suggest having a look at Jeff Moden's excellent articles, Cross Tabs and Pivots[/url].
+1
The syntax for a crosstab is so much easier to read than a PIVOT. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply