SQL: How to Pivot a Table with Pivot Columns Sharing the Same Name?

Question

I am using SQL Server 2008 R2 version 10.50.1600.1.

I'm trying to pivot a table with strings but the pivot column shares the same name. This is what my data looks like.

+------------+-----------+------------+
| patient_ID | code_type | code_value |
+------------+-----------+------------+
|          1 | ICD9      | V70        |
|          1 | ICD9      | 401.9      |
|          1 | ICD9      | 616        |
|          1 | ICD9      | 338.21     |
|          2 | ICD9      | V10        |
|          2 | ICD9      | 250        |
+------------+-----------+------------+

What I'm trying to get to is this ...

+------------+--------+--------+--------+--------+--------+--------+
| patient_id | ICD9_1 | ICD9_2 | ICD9_3 | ICD9_4 | ICD9_5 | ICD9_x |
+------------+--------+--------+--------+--------+--------+--------+
|          1 | V70    | 401.9  | 616    | 338.21 | null   | null   |
|          2 | V10    | 250    | null   | null   | null   | null   |
+------------+--------+--------+--------+--------+--------+--------+

ICD9_x can stretch to infinity because I don't know how many ICD9 codes there will be for a given patient.

Is there a way to do this in SQL?

Thanks!

Update:

Thanks for all the help! I have received two errors. It looks like pivoting requires the values to be an int because of the sum right? Is there a way to use the pivot table for string values? The ICD9 codes are all strings.

Secondly, I hit an unexpected error. It says that "the number of elements in the select list exceeds the maximum allowed number of 4096 elements." Is there a solution for a large data set?

Thanks again!

You should use dynamic pivot, here is better explained: [http://stackoverflow.com/questions/28068971/sql-pivot-table-dynamic] — Cano, Oct 30 '15 at 21:29
Try assigning something unique to the patient or encounter. a ROW_NUMBER() should get you there. As far as it stretching to infinity, you have UP TO 68000 + 13000 (ICD10 and 9 respectively). which well exceeds the 1024 max columns in SQL — Brad D, Oct 30 '15 at 21:39
Thanks all. For dynamic pivots, does it work for strings? I've searched about and all of the dynamic pivots sums the data. Apologies, I'm still very new to SQL. — Raymond, Oct 30 '15 at 23:45

score 0 · Answer 1 · edited Oct 31 '15 at 00:23

0

You can Try something like this. I hope that Works for you.

DECLARE @cols as varchar(max)
DECLARE @query as varchar(max)

select  @cols = (SELECT STUFF((SELECT ',' + QUOTENAME(t.code_type+CAST(row_number() over (partition by patient_id order by patient_id, code_type) as varchar(10)))
    FROM YOURTABLE as t
    FOR XML PATH('')),1,1,''))

select @query = 'select patient_id, ' + @cols + ' from
(select
code_type+CAST(row_number() over (partition by patient_id order by patient_id, code_type) as varchar(10)) as code_type, SUM(code_value) as code_value from YOURTABLE

group by
code_type+CAST(row_number() over (partition by patient_id order by patient_id, code_type) as varchar(10))) d

Pivot (SUM(code_value) for code_type in (' + @cols + ')) p'

EXECUTE(@query)

edited Oct 31 '15 at 00:23

Rohit Gupta

4,022
20
31
41

answered Oct 30 '15 at 22:57

thezapper

486
4
13

Thanks! Unfortunately I have a lot of patients and got this error. The number of elements in the select list exceeds the maximum allowed number of 4096 elements. Is there another way to handle a large data set? – Raymond Oct 30 '15 at 23:39
Also, in the subquery where you sum(code_value), code value is varchar. Is this still possible? I looked a a couple dynamic pivot table and all of them require summing the values. Is there a way around this? Thanks! – Raymond Oct 30 '15 at 23:56

Dimitar Kyuchukov · Accepted Answer · 2015-11-04T10:56:46.800

0

Do you have a primary key or any column to use for ordering to make sure the codes will be in the correct order?

If you have one then you can concatenate the code_type value to the output of:

row_number () OVER (PARTITION BY patient_ID, code_type ORDER BY patient_id, /* codes order column here */)

... And then you will be able to use PIVOT ( technet info ) with the concatenated values.

Here is some code to illustrate what I mean:


-- Preparing some demo data as per your sample:
DECLARE @YourTable TABLE (
    ID INT IDENTITY (1,1) PRIMARY KEY,
    patient_id INT,
    code_type VARCHAR(20),
    code_value VARCHAR(20)
)

INSERT INTO @YourTable
    (patient_id, code_type, code_value)
VALUES
    (1, 'ICD9', 'V70'),
    (1, 'ICD9', '401.9'),
    (1, 'ICD9', '616'),
    (1, 'ICD9', '338.21'),
    (2, 'ICD9', 'V10'),
    (2, 'ICD9', '250')

-- That should look like your starting point:
SELECT * FROM @YourTable

-- Now we suffix the code_type:
SELECT
    patient_id, 
    code_type + '_' + cast(
            row_number () OVER (PARTITION BY patient_id, code_type ORDER BY patient_id, ID)
            AS VARCHAR(20)
        ) AS code_type, 
    code_value
FROM
    @YourTable

-- ... and finally we pivot:
SELECT
    patient_id,
    ICD9_1, 
    ICD9_2, 
    ICD9_3, 
    ICD9_4
FROM (
    SELECT
        patient_id, 
        code_type + '_' + cast(
                row_number () OVER (PARTITION BY patient_id, code_type ORDER BY patient_id, ID)
                AS VARCHAR(20)
            ) AS code_type, 
        code_value
    FROM
        @YourTable
) data
PIVOT (
    max(code_value)
    -- you need to list all here:
    FOR code_type IN (ICD9_1, ICD9_2, ICD9_3, ICD9_4)
) piv

edited Nov 04 '15 at 10:56

answered Oct 31 '15 at 13:45

Dimitar Kyuchukov

310
1
8

Thanks! There is one more column that is a unique identifier for this table that is used as a primary key. But after pivoting it, I plan to use the patient_id as the primary key. I received an unexpected error though. It says I exceed the maximum allowed number of 4096 elements. I didn't realize there was a limit. – Raymond Nov 02 '15 at 18:46
Exceeded 4096? That sounds like you were trying to produce more than 4096 columns, while each patient seems to be on a separate row in your sample result - so to get 4096+ columns you should have had 4096+ rows per each patient on input, is that the case? Are you sure your codes counter is restarted from 1 for each patient_id? – Dimitar Kyuchukov Nov 03 '15 at 14:44
I thought that might be the case but looking at the original data, it doesn't seem possible. I have about 20k patients but the column I'm trying to pivot is only the diagnosis column, which should not exceed 20 columns when pivoted. Secondly, can I pivot varchar's? – Raymond Nov 04 '15 at 00:58
Yes, you should be able to pivot varchar - you will probably need to add an aggregation function in which case you can just use max(), but be careful - to make that work you will need to make sure the suffixed code_type is suffixed correctly (e.g. the counter / row_number resets for each customer and each row has a unique patient_id & suffixed code_type combination to avoid "hiding" data becasue of the max()). I will try to improve my post with an example to make it clear. – Dimitar Kyuchukov Nov 04 '15 at 10:40
Just updated my post with a sample to make it clear. – Dimitar Kyuchukov Nov 04 '15 at 10:58
Thanks so much! This works perfectly! – Raymond Nov 05 '15 at 18:08

SQL: How to Pivot a Table with Pivot Columns Sharing the Same Name?

2 Answers2