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?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
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;The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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) -
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply