May 25, 2016 at 3:22 am
Hi,
I have a table structure similar to the following code.
DECLARE @PatDev TABLE (
PID INT,
VisitType CHAR(2),
Rn tinyint)
INSERT @PatDev
VALUES
(1001, 'V1', 1)
,(1001, 'V2', 1)
,(1001, 'V3', 2)
,(1001, 'V4', 3)
,(1001, 'V5', 4)
,(1002, 'V1', 1)
,(1002, 'V1', 2)
,(1002, 'V2', 2)
,(1002, 'V1', 3)
,(1002, 'V1', 4)
,(1003, 'V2', 1)
,(1003, 'V1', 2)
,(1003, 'V4', 3)
,(1003, 'V3', 3)
,(1003, 'V2', 4)
,(1004, 'V2', 1)
,(1004, 'V3', 2)
,(1004, 'V5', 3)
,(1004, 'V2', 4)
,(1005, 'V2', 1)
,(1005, 'V2', 2)
,(1005, 'V2', 3)
,(1005, 'V1', 4)
,(1005, 'V5', 4)
,(1005, 'V3', 4)
SELECT *
FROM @PatDev
Expected Output:
PIDCol1Col2Col3Col4
1001V1V3V4V5
1001V2V3V4V5
1002V1V1V1V1
1002V1V2V1V1
1003V2V1V4V2
1003V2V1V3V2
1004V2V3V5V2
1005V2V2V2V1
1005V2V2V2V5
1005V2V2V2V3
Expected Output in a table
DECLARE @OutPut as table
(
PID int
, Visit1 CHAR(2)
, Visit2 CHAR(2)
, Visit3 CHAR(2)
, Visit4 CHAR(2)
)
INSERT INTO @OutPut
VALUES
(1001,'V1','V3','V4','V5')
,(1001,'V2','V3','V4','V5')
,(1002,'V1','V1','V1','V1')
,(1002,'V1','V2','V1','V1')
,(1003,'V2','V1','V4','V2')
,(1003,'V2','V1','V3','V2')
,(1004,'V2','V3','V5','V2')
,(1005,'V2','V2','V2','V1')
,(1005,'V2','V2','V2','V5')
,(1005,'V2','V2','V2','V3')
SELECT *
FROM @OutPut
Thanks in advance...
May 25, 2016 at 7:16 am
WITH a (PID,VisitType,Rn,RowNo)
AS (
SELECT PID,VisitType,Rn
,ROW_NUMBER() OVER (PARTITION BY PID,Rn ORDER BY VisitType ASC)
FROM @PatDev
),
b (PID,MaxRowNo)
AS (
SELECT PID,MAX(RowNo)
FROM a
GROUP BY PID
)
SELECT b.PID
,MAX(CASE WHEN c.C = 1 THEN ISNULL(a2.VisitType,a.VisitType) END) AS [Col1]
,MAX(CASE WHEN c.C = 2 THEN ISNULL(a2.VisitType,a.VisitType) END) AS [Col2]
,MAX(CASE WHEN c.C = 3 THEN ISNULL(a2.VisitType,a.VisitType) END) AS [Col3]
,MAX(CASE WHEN c.C = 4 THEN ISNULL(a2.VisitType,a.VisitType) END) AS [Col4]
FROM b
JOIN (SELECT R FROM (VALUES (1),(2),(3),(4)) n (R)) r
ON r.R BETWEEN 1 AND b.MaxRowNo
CROSS JOIN (SELECT C FROM (VALUES (1),(2),(3),(4)) n (C)) c
JOIN a ON a.PID = b.PID AND a.RowNo = 1 AND a.Rn = c.C
LEFT JOIN a a2 ON a2.PID = b.PID AND a2.RowNo = r.R AND a2.Rn = c.C
GROUP BY b.PID,r.R
Far away is close at hand in the images of elsewhere.
Anon.
May 25, 2016 at 11:32 am
Thanks a Lot for your solution.
That's the brilliant use of T-SQL features
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply