Sql sererver (2008R2) pivot question

Question

0.00/5 (No votes)

See more:

I have a table that I'm trying to pivot. The columns of interest are

MRN (nvarchar that represents a numerical sequence)
DX (nvarchar that represents a code)

I want to pivot the data so that I have one mrn and all of the dx codes for that mrn (there can be up to 20 dx codes per mrn).

My problem is that my result set returns all null values for the dx codes (it is understood that there's at if an MRN exists in the source data, there are 1 or more dx codes associated with it).

What am I missing?

What I have tried:

SQL

;with cte as 
(
    SELECT [MRN] as MRNPivot
           ,MRN
           ,[DX]
     FROM  [mydb].[dbo].[mytable]
)
SELECT * FROM cte
PIVOT
(
    min(mrnpivot) for [dx] in (dx1, dx2, dx3, dx4, dx5, 
                               dx6, dx7, dx8, dx9, dx10, 
                               dx11, dx12, dx13, dx14, dx15, 
                               dx16, dx17, dx18, dx19, dx20)
) AS p

My result set looks like this:

MRN  dx1   dx2   dx3.... dx20
123  NULL  NULL  NULL... NULL
124  NULL  NULL  NULL... NULL
...
...

The data looks like this:

mrnpivot mrn  dx
123      123  a105
123      123  b27
124      124  b27
124      124  1566A
124      124  a106

I want it to look like this:

mrn  dx1   dx2   dx3   dx4...  dx20
123  a105  b27   NULL  NULL... NULL
124  b27   1566A a106  NULL... NULL

Maybe a pivot isn't what I want?

Posted 27-Apr-18 2:52am

#realJSOP

Updated 27-Apr-18 4:11am

v3

Add a Solution

Comments

Maciej Los 27-Apr-18 9:41am

Strange...
I tested this:

;WITH CTE AS
(
	SELECT '01234' AS mrn, 'dx1' AS dx
	UNION ALL
	SELECT '01234', 'dx3' 
	UNION ALL
	SELECT '21234', 'dx1'
	UNION ALL
	SELECT '21234', 'dx2'
	UNION ALL
	SELECT '21234', 'dx3'
	UNION ALL
	SELECT '88234', 'dx2' 
)
SELECT *
FROM
	(
	SELECT mrn, mrn AS mrnpivot, dx 
	FROM CTE
	) AS Src 
PIVOT(MIN(mrnpivot) FOR dx IN (dx1, dx2, dx3)) AS pvt

and it works as expected.

Maciej Los 27-Apr-18 9:48am

BTW: Have you tried to see what CTE returns?

#realJSOP 27-Apr-18 10:09am

I updated my question (the cte is returning the three columns indicated from a dataset with many more columns (that are not applicable to what I'm trying to do).

Richard Deeming 27-Apr-18 9:50am

What does the source data look like?

A simplified test produces the expected output:

DECLARE @T TABLE (MRN varchar(10), DX varchar(10));

INSERT INTO @T (MRN, DX)
VALUES
    ('123', 'dx1'),
    ('123', 'dx2'),
    ('124', 'dx3'),
    ('124', 'dx4'),
    ('124', 'dx1')
;

WITH cte As
(
    SELECT
        MRN As MRNPivot,
        MRN,
        DX
    FROM
        @T
)
SELECT
    *
FROM
    cte As F
    PIVOT
    (
        Min(MRNPivot)
        FOR DX In (dx1, dx2, dx3, dx4)
    ) As p
;

Output:

MRN | dx1  | dx2  | dx3  | dx4
-------------------------------
123 | 123  | 123  | NULL | NULL
124 | 124  | NULL | 124  | 124

#realJSOP 27-Apr-18 10:11am

I updated my question.

Richard Deeming 27-Apr-18 10:13am

Yeah, I don't think a PIVOT is what you want. :)

What's the sequence for the values slotting in to the "dx" columns?

#realJSOP 27-Apr-18 10:17am

The sequence? The order is not important (from what I can see in the example result that I was provided).

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2018-04-27T04:22:00

Assuming you want the DX values in ascending order, something like this should work:

SQL

WITH cte As
(
    SELECT
        MRN,
        DX,
        ROW_NUMBER() OVER (PARTITION BY MRN ORDER BY DX) As RN
     FROM
        [mydb].[dbo].[mytable]
)
SELECT
    MRN,
    Max(CASE RN WHEN 1 THEN DX END) As DX1,
    Max(CASE RN WHEN 2 THEN DX END) As DX2,
    Max(CASE RN WHEN 3 THEN DX END) As DX3,
    Max(CASE RN WHEN 4 THEN DX END) As DX4,
    Max(CASE RN WHEN 5 THEN DX END) As DX5,
    Max(CASE RN WHEN 6 THEN DX END) As DX6,
    Max(CASE RN WHEN 7 THEN DX END) As DX7,
    Max(CASE RN WHEN 8 THEN DX END) As DX8,
    Max(CASE RN WHEN 9 THEN DX END) As DX9,
    Max(CASE RN WHEN 10 THEN DX END) As DX10,
    Max(CASE RN WHEN 11 THEN DX END) As DX11,
    Max(CASE RN WHEN 12 THEN DX END) As DX12,
    Max(CASE RN WHEN 13 THEN DX END) As DX13,
    Max(CASE RN WHEN 14 THEN DX END) As DX14,
    Max(CASE RN WHEN 15 THEN DX END) As DX15,
    Max(CASE RN WHEN 16 THEN DX END) As DX16,
    Max(CASE RN WHEN 17 THEN DX END) As DX17,
    Max(CASE RN WHEN 18 THEN DX END) As DX18,
    Max(CASE RN WHEN 19 THEN DX END) As DX19,
    Max(CASE RN WHEN 20 THEN DX END) As DX20
FROM 
    cte
GROUP BY
    MRN
;

Given your sample data, this will produce:

MRN | DX1   | DX2   | DX3   | DX4   | ...
-----------------------------------------
123 | a105  | b27   | NULL  | NULL  | ...
124 | 1566A | a106  | b27   | NULL  | ...