Pivot: Rows to Column and Values as rows
-
Hi, I have table which has the following information
Source_Table:
Expected Result:
Tried the below Query, but doesn't give me the expected result.
SELECT
ReferenceNo,FYPeriod,POValue
FROM
(
SELECT
D.FormBuilderID,
D.DocumentID,
D.ColumnName,
D.ColumnValue,
Setno = ROW_NUMBER() OVER (PARTITION BY D.DocumentID, D.ColumnName ORDER BY D.FormBuilderID)
FROM dbo.Data AS D
) AS S
PIVOT
(
MAX(S.ColumnValue) FOR ColumnName IN (TenderNo,FYPeriod,POValue)
) AS P;
-
There's nothing in the original data you've posted to preserve the required order of the data to do this. Your addition of a calculated row number doesn't cut it for the same reason. There has to be another column that clearly identifies the correct order or the effective temporal notation of each row. Any attempts without one of those will lead to incorrect returns.
Also, please help us help you. Please read the article at the first link in my signature line below for all future code based questions. Thanks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff, Thanks your note. I have added one more column and it is working now.
-
It would be nice if you told us what column you added and what it contains. It would also be nice if you posted readily consumable data so we can test what you've done and, possibly, demonstrate a better way.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Without something that ties the specific values together (eg "ABCD1234" belongs with "£133.00") then it's going to be a crap shoot as far as what values get paired up.
Based on what you have displayed, here is what you can do...
IF OBJECT_ID('tempdb..#OrigTable', 'U') IS NOT NULL
BEGIN DROP TABLE #OrigTable; END;
CREATE TABLE #OrigTable (
FormBuilderID int,
DocumentID int,
ColumnValue varchar(50),
ColumnName varchar(50)
);
INSERT #OrigTable (FormBuilderID, DocumentID, columnValue, ColumnName) VALUES
(1001,1,'ABCD1234','ReferenceNo'),
(1001,1,'ABCD1235','ReferenceNo'),
(1002,1,'FY2019/2020','FYPeriod'),
(1002,1,'FY2019/2020','FYPeriod'),
(1003,1,'£133.00','POValue'),
(1003,1,'£1,450.00','POValue');
--===================================================
WITH
cte_ReferenceNo AS (
SELECT
ot.*,
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
#OrigTable ot
WHERE
ot.ColumnName = 'ReferenceNo'
),
cte_FYPeriod AS (
SELECT
ot.*,
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
#OrigTable ot
WHERE
ot.ColumnName = 'FYPeriod'
),
cte_POValue AS (
SELECT
ot.*,
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
#OrigTable ot
WHERE
ot.ColumnName = 'POValue'
)
SELECT
ReferenceNo = ro.ColumnValue,
FYPeriod = fp.ColumnValue,
POValue = pv.ColumnValue
FROM
cte_ReferenceNo ro
JOIN cte_FYPeriod fp
ON ro.DocumentID = fp.DocumentID
AND ro.rn = fp.rn
JOIN cte_POValue pv
ON ro.DocumentID = pv.DocumentID
AND ro.rn = pv.rn;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply