October 10, 2014 at 11:45 pm
I have a sql statement that return the following tables
Doc_ID Code
1000_3703 715.90_Osteoarthros NOS-unspec_1
1000_3703 477.9_Allergic rhinitis NOS_2
1000_4611 788.30_Urinary incontinence NOS_3
1000_4611 715.00_General osteoarthrosis_1
1000_4611 564.00_Constipation NOS_2
1000_4611 496_Chr airway obstruct NEC_2
I need help with the statement with pivot so that it would appear like
Doc_ID Code_1 Code_2 Code_3 Code_4 Code_5 Code _6
1000_3703 715.90_Osteoarthros NOS-unspec_1 477.9_Allergic rhinitis NOS_2
1000_4611 788.30_Urinary incontinence NOS_3 715.00_General osteoarthrosis_1 564.00_Constipation NOS_2 496_Chr airway obstruct NEC_2
The number of column grow as the number of row of Doc_ID expands
Thank you very in advanced.
October 11, 2014 at 2:05 am
Quick (semi) dynamic sql approach, simple and self explanatory
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;
CREATE TABLE dbo.TBL_SAMPLE_DATA
(
Doc_ID VARCHAR(20) NOT NULL
,Code VARCHAR(100) NOT NULL
);
INSERT INTO dbo.TBL_SAMPLE_DATA(Doc_ID,Code)
VALUES
('1000_3703','715.90_Osteoarthros NOS-unspec_1' )
,('1000_3703','477.9_Allergic rhinitis NOS_2' )
,('1000_4611','788.30_Urinary incontinence NOS_3')
,('1000_4611','715.00_General osteoarthrosis_1' )
,('1000_4611','564.00_Constipation NOS_2' )
,('1000_4611','496_Chr airway obstruct NEC_2' );
DECLARE @SQL_STR NVARCHAR(MAX) = N';WITH COLUMNIZED_DATA(CD_RID,Doc_ID,Code) AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY BD.Doc_ID
ORDER BY (SELECT NULL)
) AS CD_RID
,BD.Doc_ID
,BD.Code
FROM dbo.TBL_SAMPLE_DATA BD
)
SELECT
CD.Doc_ID
{{@COL_LIST}}
FROM COLUMNIZED_DATA CD
GROUP BY CD.Doc_ID';
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,MAX_COL_COUNT(COL_COUNT) AS
(
SELECT TOP(1)
COUNT(BD.Code) AS COL_COUNT
FROM dbo.TBL_SAMPLE_DATA BD
GROUP BY BD.Doc_ID
ORDER BY 1 DESC
)
,NUMS(N) AS
(
SELECT TOP(SELECT COL_COUNT FROM MAX_COL_COUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM T T1,T T2,T T3,T T4,T T5
)
SELECT @SQL_STR = REPLACE(@SQL_STR,N'{{@COL_LIST}}',
( SELECT
N',MAX(CASE WHEN CD_RID = ' + CAST(NM.N AS VARCHAR(12))
+ N' THEN CD.Code END) AS COL_' + CAST(NM.N AS VARCHAR(12)) + NCHAR(13) + NCHAR(10)
FROM NUMS NM
FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)'))
EXEC (@SQL_STR);
Results
Doc_ID COL_1 COL_2 COL_3 COL_4
----------- ---------------------------------- --------------------------------- -------------------------- ------------------------------
1000_3703 715.90_Osteoarthros NOS-unspec_1 477.9_Allergic rhinitis NOS_2 NULL NULL
1000_4611 788.30_Urinary incontinence NOS_3 715.00_General osteoarthrosis_1 564.00_Constipation NOS_2 496_Chr airway obstruct NEC_2
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply