September 27, 2011 at 9:04 am
Can someone help me with the query how to transpose date rows into columns dynamically?
i'm using sql server 2008 express edition. i read pivot and unpivot, but unable to acheive the results that i want.
for the report, i need to transpose the measurment_date rows into columns (upto 5 dates).
so the end results i should have is as below columns -
lesion_nbr, measurement_site_desc, measurement_date1, measurement_date2, measurement_date3, ...upto 5 dates
Any help is greately appreciated!
below is the create sample table, and query, and it's results
CREATE TABLE #Target_Lesions(
MRN varchar(15) NULL,
Measurement_Date date NOT NULL,
Measurement_Site_desc varchar(50) NULL,
Measurement_value varchar(15) NULL,
Measurement_Unit varchar(5) NULL
)
INSERT INTO #Target_Lesions Values
('a123','2011-08-31', 'left perirectal mass', '1.1', 'cm'),
('a123','2011-08-31', 'soft tissue base of bladder', '2.6', 'cm'),
('a123','2011-08-31', 'left sidewall mass', '1.9', 'cm'),
('a123','2011-09-05', 'left perirectal mass', '2.2', 'cm'),
('a123','2011-09-05', 'soft tissue base of bladder', '2.7', 'cm'),
('a123','2011-09-05', 'left sidewall mass', '1.8', 'cm')
select ROW_NUMBER() over(order by Measurement_Date) as lesion_nbr
,Measurement_Date, Measurement_Site_desc
, measurement_value + ' ' + Measurement_Unit as measure_value_unit
from #Target_Lesions
where MRN = 'a123'
order by Measurement_Date
Results:
lesion_nbrMeasurement_Date Measurement_Site_descmeasure_value_unit
1 2011-08-31 left perirectal mass1.1 cm
2 2011-08-31 soft tissue base of bladder2.6 cm
3 2011-08-31 left sidewall mass 1.9 cm
4 2011-09-05 left perirectal mass2.2 cm
5 2011-09-05 soft tissue base of bladder2.7 cm
6 2011-09-05 left sidewall mass 1.8 cm
September 27, 2011 at 9:12 am
Hello and welcome to SSC!
It seems that your DDL script seems to have become detached from your post, or perhaps you were unaware of the benefits of providing one.
When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Thanks!
September 27, 2011 at 9:38 am
sorry, i'm newbie and this is my first time posting my question.
anyway, i updated my original post, that has the flow of creating the sample table, test data, and query with results
let me know if it makes sense and can help
September 27, 2011 at 9:40 am
preetid2 (9/27/2011)
sorry, i'm newbie and this is my first time posting my question.anyway, i updated my original post, that has the flow of creating the sample table, test data, and query with results
let me know if it makes sense and can help
Yep, that's much better 😀
OK, now what are the desired result from the sample you've provided? Are you grouping by the "Measurement_Site_desc" ?
September 27, 2011 at 9:53 am
yes, i need to group by Measurement_Site_desc, and display Measurement_Date in columns for upto 5 dates.
the desired result should be something like this...
desired results...
Measurement_Site_desc 8/31/20119/5/2011 Date 3 Date4 Date5
left perirectal mass 1.1 cm 2.2 cm
soft tissue base of bladder2.6 cm 2.7 cm
left sidewall mass 1.9 cm 1.8 cm
September 27, 2011 at 10:12 am
preetid2 (9/27/2011)
yes, i need to group by Measurement_Site_desc, and display Measurement_Date in columns for upto 5 dates.the desired result should be something like this...
desired results...
Measurement_Site_desc 8/31/20119/5/2011 Date 3 Date4 Date5
left perirectal mass 1.1 cm 2.2 cm
soft tissue base of bladder2.6 cm 2.7 cm
left sidewall mass 1.9 cm 1.8 cm
I've got to go soon, so someone else will undoubtedly pick this up before I get back to it.
Just to clear it up, does this look correct as your expected result?
SELECT 'left perirectal mass' AS Measurement_Site_desc,
'1.1 cm' AS [8/31/2011], '2.2 cm' AS [9/5/2011], NULL AS [Date 3],
NULL AS [Date 4], NULL AS [Date5]
UNION ALL
SELECT 'soft tissue base of bladder', '2.6 cm', '2.7 cm', NULL, NULL, NULL
UNION ALL
SELECT 'left sidewall mass', '1.9 cm', '1.8 cm', NULL, NULL, NULL
/******** Produces the following result set ***********
Measurement_Site_desc 8/31/2011 9/5/2011 Date 3 Date 4 Date5
--------------------------- --------- -------- ----------- ----------- -----------
left perirectal mass 1.1 cm 2.2 cm NULL NULL NULL
soft tissue base of bladder 2.6 cm 2.7 cm NULL NULL NULL
left sidewall mass 1.9 cm 1.8 cm NULL NULL NULL
\******** ********************************** ***********/
If that looks right, try these articles (Part 1[/url], Part 2[/url]) which I think explain what you want to do.
September 27, 2011 at 11:05 am
This is exactly what the end result should looks like!!
except i need to produce it dynamically. i looked at the articles, and i think Part2 (Cross tabs and pivot2- Dynamic Cross tabs) is the one that i need to follow. but i am not able to understand how to use that for my query.
September 28, 2011 at 2:35 am
preetid2 (9/27/2011)
This is exactly what the end result should looks like!!except i need to produce it dynamically. i looked at the articles, and i think Part2 (Cross tabs and pivot2- Dynamic Cross tabs) is the one that i need to follow. but i am not able to understand how to use that for my query.
Sorry, had to dash yesterday but expected someone else to pick this up. Anyway, here goes 🙂
--Conditionally drop temp table, helps for re-running
IF object_id('tempdb..#Target_Lesions') IS NOT NULL
DROP TABLE #Target_Lesions
CREATE TABLE #Target_Lesions(
MRN varchar(15) NULL,
Measurement_Date date NOT NULL,
Measurement_Site_desc varchar(50) NULL,
Measurement_value varchar(15) NULL,
Measurement_Unit varchar(5) NULL
)
INSERT INTO #Target_Lesions Values
('a123','2011-08-31', 'left perirectal mass', '1.1', 'cm'),
('a123','2011-08-31', 'soft tissue base of bladder', '2.6', 'cm'),
('a123','2011-08-31', 'left sidewall mass', '1.9', 'cm'),
('a123','2011-09-05', 'left perirectal mass', '2.2', 'cm'),
('a123','2011-09-05', 'soft tissue base of bladder', '2.7', 'cm'),
('a123','2011-09-05', 'left sidewall mass', '1.8', 'cm')
Now, first lets write the actual code we want to produce to get your results.
SELECT Measurement_Site_desc,
NULLIF(MAX(CASE
WHEN Measurement_Date = '2011-08-31'
THEN measure_value_unit
ELSE ''
END), '') AS [2011-08-31],
NULLIF(MAX(CASE
WHEN Measurement_Date = '2011-09-05'
THEN measure_value_unit
ELSE ''
END), '') AS [2011-09-05],
NULLIF(MAX(CASE
WHEN Measurement_Date = ''
THEN measure_value_unit
ELSE ''
END), '') AS [Date 3],
NULLIF(MAX(CASE
WHEN Measurement_Date = ''
THEN measure_value_unit
ELSE ''
END), '') AS [Date 4],
NULLIF(MAX(CASE
WHEN Measurement_Date = ''
THEN measure_value_unit
ELSE ''
END), '') AS [Date 5]
FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY Measurement_Date
) AS lesion_nbr
,Measurement_Date
,Measurement_Site_desc
,measurement_value + ' ' + Measurement_Unit AS measure_value_unit
FROM #Target_Lesions
WHERE MRN = 'a123'
) a
GROUP BY Measurement_Site_desc
ORDER BY MIN(lesion_nbr)
/**** Produces ******
Measurement_Site_desc 2011-08-31 2011-09-05 Date 3 Date 4 Date 5
-------------------------------------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
left perirectal mass 1.1 cm 2.2 cm NULL NULL NULL
soft tissue base of bladder 2.6 cm 2.7 cm NULL NULL NULL
left sidewall mass 1.9 cm 1.8 cm NULL NULL NULL
\********************/
OK, looking good. Now we need to create that more dynamically, to include whatever dates happen to come through. Bear in mind, I'm literally following the article I pointed you to, so if you follow through then hopefully you'll end with a greater understanding.
--===== Declare the variables that will contain the dynamic SQL
DECLARE @SQL1 NVARCHAR(4000),
@SQL2 NVARCHAR(4000),
@SQL3 NVARCHAR(4000)
--===== Create the "static" section of the code
SELECT @SQL1 = 'SELECT Measurement_Site_desc,'+CHAR(10)
;
--===== The "Hard" Part - Concatenation to Form the Date Columns
WITH CTE AS (
SELECT ROW_NUMBER() OVER (
ORDER BY Measurement_Date
) AS nbrDates
,CONVERT(NVARCHAR(10), Measurement_Date) AS Measurement_Date
,1 AS pri
FROM #Target_Lesions
WHERE MRN = 'a123'
GROUP BY Measurement_Date
UNION ALL
SELECT 1,'Date 1',0
UNION ALL
SELECT 2,'Date 2',0
UNION ALL
SELECT 3,'Date 3',0
UNION ALL
SELECT 4,'Date 4',0
UNION ALL
SELECT 5,'Date 5',0)
SELECT @SQL2 = COALESCE(@SQL2, '') + '
NULLIF(MAX(CASE WHEN CONVERT(NVARCHAR(10),Measurement_Date) = ' + QUOTENAME(d.Measurement_Date, '''') +
' THEN measure_value_unit ELSE '''' END),'''') AS [' + d.Measurement_Date + '],' + CHAR(10)
FROM (
--==== Filter Results
SELECT CONVERT(NVARCHAR(10),b.Measurement_Date) AS Measurement_Date
FROM (
SELECT a.nbrDates
,MAX(a.pri) AS pri
FROM CTE a
GROUP BY a.nbrDates
) c
INNER JOIN CTE b ON c.pri = b.pri
AND c.nbrDates = b.nbrDates
) d
ORDER BY d.Measurement_Date
SET @SQL2 = SUBSTRING(@SQL2, 1, LEN(@SQL2) - 2) + CHAR(10)
--===== Create the "Mostly Static" section of the code
SELECT @SQL3 =
'FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY Measurement_Date
) AS lesion_nbr
,Measurement_Date
,Measurement_Site_desc
,measurement_value + '''+CHAR(32)+''' + Measurement_Unit AS measure_value_unit
FROM #Target_Lesions
WHERE MRN = ''a123''
) a
GROUP BY Measurement_Site_desc
ORDER BY MIN(lesion_nbr)'
EXEC (@SQL1 + @SQL2 + @SQL3)
Which produces. . . .
Measurement_Site_desc 2011-08-31 2011-09-05 Date 3 Date 4 Date 5
-------------------------------------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
left perirectal mass 1.1 cm 2.2 cm NULL NULL NULL
soft tissue base of bladder 2.6 cm 2.7 cm NULL NULL NULL
left sidewall mass 1.9 cm 1.8 cm NULL NULL NULL
(3 row(s) affected)
September 28, 2011 at 6:02 am
hope u got ur answer from cad, although i found this post may be useful for you.
September 28, 2011 at 11:53 am
Thanks a lot Cad !!!
you are super good!! i slowely re-read the part2 article and stepped thru each step that you explained in the above code. finally now i undertsood the steps.
Thanks for the link Shyam !!
November 14, 2011 at 12:25 pm
Hi, need help again with the code. after i got this built and showed to the user. the user said the Dates are not always 5 dates. there will be more than 5 dates. which means the Dates in the column header is not limited to 5, but they need to created dynamically for the number of dates the data is available for. so it could be 2,3,5,6,...and more. no max.
can someone help me please how can i accomplish this from the sql stored proc?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply