SQL help with Pivot
-
I am trying to use PIVOT to convert rows into columns.
Following is the sample code with some data. I want to get one row for each VID, OrdDate
However, the following code is not creating one row for each VID, OrdDate. It keeps them as is and I am looking for 2 records. Any help is greatly appreciated.
CREATE TABLE #t1([VID] [varchar](100) NULL, [OrdDate] [datetime] NOT NULL, [QID] [varchar](50) NULL, [Response] [varchar](50) NULL, [ODate2] [datetime]) ON [PRIMARY]
insert into #t1 values('11111','2022-01-10 13:37:06.000','RA Score','0 and calm','2022-02-11 10:00:00.000')
insert into #t1 values('11111','2022-01-10 13:37:06.000','RA Score','drowsy','2022-02-11 10:07:00.000')
insert into #t1 values('11111','2022-01-10 13:37:06.000','RA Score','+2 agitated','2022-02-11 10:08:00.000')
insert into #t1 values('11111','2022-01-10 13:37:06.000','O2','95','2022-02-11 10:23:00.000')
insert into #t1 values('11111','2022-01-10 13:37:06.000','RA Score','-3 moderate sedation','2022-02-11 11:11:00.000')
insert into #t1 values('11111','2022-01-10 13:37:06.000','Res rate','16','2022-02-11 14:00:00.000')
insert into #t1 values('11111','2022-01-10 13:37:06.000','Res rate','32','2022-02-11 14:23:00.000')
insert into #t1 values('11111','2022-01-10 13:37:06.000','BD pressure','141/87','2022-02-11 15:00:00.000')
insert into #t1 values('22222','2022-01-10 13:37:06.000','RA Score','0 and calm','2022-02-11 10:00:00.000')
insert into #t1 values('22222','2022-01-10 13:37:06.000','RA Score','-1 drowsy','2022-02-11 10:07:00.000')
insert into #t1 values('22222','2022-01-10 13:37:06.000','RA Score','+2 agitated','2022-02-11 10:08:00.000')
insert into #t1 values('22222','2022-01-10 13:37:06.000','O2','95','2022-02-11 10:23:00.000')
insert into #t1 values('22222','2022-01-10 13:37:06.000','RA Score','-3 moderate sedation','2022-02-11 11:11:00.000')
insert into #t1 values('22222','2022-01-10 13:37:06.000','Res rate','16','2022-02-11 14:00:00.000')
insert into #t1 values('22222','2022-01-10 13:37:06.000','Res rate','32','2022-02-11 14:23:00.000')
insert into #t1 values('22222','2022-01-10 13:37:06.000','BD pressure','141/87','2022-02-11 15:00:00.000')
Select [VID], [OrdDate],
[C Result],[RA Score],[PP score],[Temp],[Res rate],[O2],[BD pressure]
from #t1
PIVOT(MAX([Response])
FOR [QID] in ([C Result],[RA Score],[PP score],[Temp],[Res rate],[O2],[BD pressure])) as P
drop table #t1 -
Can you also post an image showing the results you would like to see, based on this data?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Please see the attachment
-
-
Try this:
SELECT VID
,OrdDate
,CResult = MAX(IIF(QID = 'C Result', Response, NULL))
,RAScore = MAX(IIF(QID = 'RA Score', Response, NULL))
,PPScore = MAX(IIF(QID = 'PP Score', Response, NULL))
,Temp = MAX(IIF(QID = 'Temp', Response, NULL))
,ResRate = MAX(IIF(QID = 'Res rate', Response, NULL))
,O2 = MAX(IIF(QID = 'O2', Response, NULL))
,BDPressure = MAX(IIF(QID = 'BD pressure', Response, NULL))
FROM #t1
GROUP BY VID
,OrdDate;If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
or the one below - but output will never be the one you desire
Select *
from (select distinct VID
, QID
, OrdDate
, Response from #t1) t1
PIVOT(MAX([Response])
FOR [QID] in ([C Result],[RA Score],[PP score],[Temp],[Res rate],[O2],[BD pressure])) as P
order by vid -
I would think you'd want the last Response rather than just picking one seemingly at random (as in your results) or the value that happened to be the MAX one (in the other posters' code).
;WITH cte_get_last_values AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY VID, OrdDate, QID ORDER BY ODate2 DESC) AS row_num
FROM #t1
)
SELECT
VID, OrdDate,
MAX(CASE WHEN QID = 'C Result' THEN Response END) AS [C Result],
MAX(CASE WHEN QID = 'RA Score' THEN Response END) AS [RA Score],
MAX(CASE WHEN QID = 'PP Score' THEN Response END) AS [PP core],
MAX(CASE WHEN QID = 'Temp' THEN Response END) AS [Temp],
MAX(CASE WHEN QID = 'Res Rate' THEN Response END) AS [Res Rate],
MAX(CASE WHEN QID = 'O2' THEN Response END) AS [O2],
MAX(CASE WHEN QID = 'BD Pressure' THEN Response END) AS [BD Pressure]
FROM cte_get_last_values
WHERE row_num = 1
GROUP BY VID, OrdDate
ORDER BY VID, OrdDateSQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
Great. Thanks for all the replies. Much appreciated.
-
This was removed by the editor as SPAM
-
JohnnyCantrell wrote:
Thanks for the suggestions.
Hi and welcome aboard.
Be advised that your post looks and smells like a precursor to spam. If you're a spammer, please go away. We're watching.
If your not a spammer then, like I said, welcome aboard. You'll find some interesting stuff here.
--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)
Intro to Tally Tables and Functions
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply