June 12, 2017 at 7:48 am
I have data in this format and i need to pivot it.
create table #temp(ID int, YDate int, DE varchar(200), KeyValue varchar(200), ReasonType varchar(200), ReasonText varchar(200))
insert into #temp
values (1, 20170101, 'A', 'CDN', 'Reason Text 1', null),
(1, 20170101, 'B', 'CDN', 'Reason Text 1', null),
(1, 20170101, 'C', 'CDN', 'Reason Text 2', null),
(2, 20170101, 'A', 'USA', 'Reason Text 1', 'Other 1'),
(2, 20170101, 'B', 'USA', 'Reason Text 2', 'Other 2'),
(2, 20170101, 'C', 'USA', 'Reason Text 3', 'Other 3'),
(3, 20170101, 'A', 'RSA', 'Reason Text 2', null),
(3, 20170101, 'B', 'RSA', 'Reason Text 2', null),
(3, 20170101, 'C', 'RSA', 'Reason Text 2', null)
and my expected result set will be like this.
select ID, YDate, A_KeyValue, A_ReasonType, A_ReasonText, B_KeyValue, B_ReasonType, B_ReasonText, C_KeyValue, C_ReasonType, C_ReasonText
I really appreciate if any one able to help
Thanks
June 12, 2017 at 7:58 am
inayatkhan - Monday, June 12, 2017 7:48 AMI have data in this format and i need to pivot it.
create table #temp(ID int, YDate int, DE varchar(200), KeyValue varchar(200), ReasonType varchar(200), ReasonText varchar(200))
insert into #temp
values (1, 20170101, 'A', 'CDN', 'Reason Text 1', null),
(1, 20170101, 'B', 'CDN', 'Reason Text 1', null),
(1, 20170101, 'C', 'CDN', 'Reason Text 2', null),
(2, 20170101, 'A', 'USA', 'Reason Text 1', 'Other 1'),
(2, 20170101, 'B', 'USA', 'Reason Text 2', 'Other 2'),
(2, 20170101, 'C', 'USA', 'Reason Text 3', 'Other 3'),
(3, 20170101, 'A', 'RSA', 'Reason Text 2', null),
(3, 20170101, 'B', 'RSA', 'Reason Text 2', null),
(3, 20170101, 'C', 'RSA', 'Reason Text 2', null)and my expected result set will be like this.
select ID, YDate, A_KeyValue, A_ReasonType, A_ReasonText, B_KeyValue, B_ReasonType, B_ReasonText, C_KeyValue, C_ReasonType, C_ReasonTextI really appreciate if any one able to help
Thanks
Will you always have A, B & C? Or will the values vary?
June 12, 2017 at 9:10 am
Different values
June 12, 2017 at 9:52 am
Try this...
IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
ID INT,
YDate INT,
DE VARCHAR(200),
KeyValue VARCHAR(200),
ReasonType VARCHAR(200),
ReasonText VARCHAR(200)
);
INSERT INTO #temp
VALUES
(1, 20170101, 'A', 'CDN', 'Reason Text 1', NULL),
(1, 20170101, 'B', 'CDN', 'Reason Text 1', NULL),
(1, 20170101, 'C', 'CDN', 'Reason Text 2', NULL),
(2, 20170101, 'A', 'USA', 'Reason Text 1', 'Other 1'),
(2, 20170101, 'B', 'USA', 'Reason Text 2', 'Other 2'),
(2, 20170101, 'C', 'USA', 'Reason Text 3', 'Other 3'),
(3, 20170101, 'A', 'RSA', 'Reason Text 2', NULL),
(3, 20170101, 'B', 'RSA', 'Reason Text 2', NULL),
(3, 20170101, 'C', 'RSA', 'Reason Text 2', NULL);
SELECT
t.ID,
t.YDate,
A_KeyValue = MAX(CASE WHEN t.DE = 'A' THEN t.KeyValue END),
A_ReasonType = MAX(CASE WHEN t.DE = 'A' THEN t.ReasonType END),
A_ResonText = MAX(CASE WHEN t.DE = 'A' THEN t.ReasonText END),
B_KeyValue = MAX(CASE WHEN t.DE = 'B' THEN t.KeyValue END),
B_ReasonType = MAX(CASE WHEN t.DE = 'B' THEN t.ReasonType END),
B_ResonText = MAX(CASE WHEN t.DE = 'B' THEN t.ReasonText END),
C_KeyValue = MAX(CASE WHEN t.DE = 'C' THEN t.KeyValue END),
C_ReasonType = MAX(CASE WHEN t.DE = 'C' THEN t.ReasonType END),
C_ResonText = MAX(CASE WHEN t.DE = 'C' THEN t.ReasonText END)
-- continue pattern as needed...
FROM
#temp t
GROUP BY
t.ID,
t.YDate;
June 12, 2017 at 10:01 am
Issue is DE not always "A","B","C"
June 12, 2017 at 10:05 am
inayatkhan - Monday, June 12, 2017 10:01 AMIssue is DE not always "A","B","C"
ok....so please provide sample data that gives us ALL the possibilities.....
will you only EVER require to return three "sets" or will there be times when you require more ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 12, 2017 at 10:14 am
If you need the dynamic version, try the following...
IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
ID INT,
YDate INT,
DE VARCHAR(200),
KeyValue VARCHAR(200),
ReasonType VARCHAR(200),
ReasonText VARCHAR(200)
);
INSERT INTO #temp
VALUES
(1, 20170101, 'A', 'CDN', 'Reason Text 1', NULL),
(1, 20170101, 'B', 'CDN', 'Reason Text 1', NULL),
(1, 20170101, 'C', 'CDN', 'Reason Text 2', NULL),
(2, 20170101, 'A', 'USA', 'Reason Text 1', 'Other 1'),
(2, 20170101, 'B', 'USA', 'Reason Text 2', 'Other 2'),
(2, 20170101, 'C', 'USA', 'Reason Text 3', 'Other 3'),
(3, 20170101, 'A', 'RSA', 'Reason Text 2', NULL),
(3, 20170101, 'B', 'RSA', 'Reason Text 2', NULL),
(3, 20170101, 'C', 'RSA', 'Reason Text 2', NULL);
--=======================================================
DECLARE
@PivotSQL NVARCHAR(MAX) = N'',
@DeBug BIT = 1; --<<< set to 0 to execute, set to 1 to print debug code...
SELECT
@PivotSQL = CONCAT(@PivotSQL, N',
', t.DE, N'_KeyValue = MAX(CASE WHEN t.DE = ''', t.DE, N''' THEN t.KeyValue END),
', t.DE, N'_ReasonType = MAX(CASE WHEN t.DE = ''', t.DE, N''' THEN t.ReasonType END),
', t.DE, N'_ResonText = MAX(CASE WHEN t.DE = ''', t.DE, N''' THEN t.ReasonText END)')
FROM (
SELECT t.DE FROM #temp t GROUP BY t.DE
) t
ORDER BY
t.DE;
SET @PivotSQL = CONCAT(N'
SELECT
t.ID,
t.YDate,',
STUFF(@PivotSQL, 1, 2, ''), N'
FROM
#temp t
GROUP BY
t.ID,
t.YDate;'
)
IF @DeBug = 1
BEGIN
PRINT(@PivotSQL);
END;
ELSE
BEGIN
EXEC sys.sp_executesql @PivotSQL;
END;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply