April 18, 2017 at 4:54 pm
Hi,
Can anyone help me writing the code without using the CONCAT as we need the script to run on 2008 and 2008 SQL does not support CONCAT.
IF OBJECT_ID('Tempdb..#temp') IS NOT NULL DROP TABLE #temp
CREATE TABLE #temp (DiagnosisBridgeKey int, DiagnosisKey int, DiagnosisCode varchar(10))
INSERT INTO #temp VALUES (2,111,'145.9')
INSERT INTO #temp VALUES (2,112,'17.43')
INSERT INTO #temp VALUES (2,113,'17.84')
INSERT INTO #temp VALUES (2,114,'196.2')
INSERT INTO #temp VALUES (2,115,'202.81')
INSERT INTO #temp VALUES (2,116,'204.21')
INSERT INTO #temp VALUES (2,117,'249.71')
INSERT INTO #temp VALUES (2,118,'263.8')
INSERT INTO #temp VALUES (2,119,'145.9')
INSERT INTO #temp VALUES (2,120,'269.8')
INSERT INTO #temp VALUES (2,121,'276.7')
SELECT DiagnosisBridgeKey AS DiagnosisBridgeKey, DiagnosisCode1,DiagnosisCode2,DiagnosisCode3,DiagnosisCode4,DiagnosisCode5,DiagnosisCode6,DiagnosisCode7,DiagnosisCode8,DiagnosisCode9,DiagnosisCode10,
DiagnosisCode11,DiagnosisCode12,DiagnosisCode13,DiagnosisCode14,DiagnosisCode15,DiagnosisCode16,DiagnosisCode17,DiagnosisCode18,DiagnosisCode19,DiagnosisCode20
FROM (
SELECT dxb.DiagnosisBridgeKey, dxb.DiagnosisCode, SortOrder = CONCAT('DiagnosisCode',ROW_NUMBER() OVER(PARTITION BY DiagnosisBridgeKey ORDER BY dxb.DiagnosisKey))
FROM #temp dxb
) dxb
PIVOT (
MIN(DiagnosisCode)
FOR SortOrder IN (DiagnosisCode1,DiagnosisCode2,DiagnosisCode3,DiagnosisCode4,DiagnosisCode5,DiagnosisCode6,DiagnosisCode7,DiagnosisCode8,DiagnosisCode9,DiagnosisCode10,
DiagnosisCode11,DiagnosisCode12,DiagnosisCode13,DiagnosisCode14,DiagnosisCode15,DiagnosisCode16,DiagnosisCode17,DiagnosisCode18,DiagnosisCode19,DiagnosisCode20)
) pvt
Thanks in advance
S
April 18, 2017 at 5:24 pm
Did you read Jeff Moden's article about crosstabs/pivots in T-SQL?
April 18, 2017 at 6:06 pm
But my scenario does not have a seggregation or datefields in it.
April 18, 2017 at 7:01 pm
Could you post your expected completed pivot given the data provided?
April 18, 2017 at 10:23 pm
Do you understand the code?
CONCAT is simply a method to concatenate a number of string (or values that can be implicitly converted to string) values.
How would you normally join a string value and an int value in SQL 2008?
You need to replace
SortOrder = CONCAT('DiagnosisCode',ROW_NUMBER() OVER(PARTITION BY DiagnosisBridgeKey ORDER BY dxb.DiagnosisKey))
with
SortOrder = 'DiagnosisCode' + CONVERT(VARCHAR(10), ROW_NUMBER() OVER(PARTITION BY DiagnosisBridgeKey ORDER BY dxb.DiagnosisKey))
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply