Query to change the way data is looked at

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Query to change the way data is looked at

Author

Topic

michaeleisenstein
Starting Member

4 Posts

Posted - 2011-05-11 : 06:42:07

Hi All

Hoping someone can help me on this.

I have a table which contains data on patients. This table contains visits for a patient on multiple dates.

So for example i have a table which contains the clinic number of the patient ( Field1 ) and the date of the visit ( Field2 )

Field1 Field2

WC50001 01/01/2010
WC50001 05/03/2010
WC50001 08/09/2010
WC50002 01/01/2011
WC50002 08/05/2011

What my customer would like to see is the data laid out in this format:

Field1: Date1: Date2: Date3:
WC50001 01/01/2010 05/03/2010 08/09/2010
WC50002 01/01/2011 08/05/2011

Is there a way of doing this. Using pivots causes the column names to be renamed to the actual date values which is not what they want. They want to see a single record per patient with each of the dates they came displayed in columns next to each other. It also needs to be dynamic in the sense that the number of columns needs to increase since some patients might have only ever come once and other multiple times. Hope someone can help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-11 : 07:05:29

Can you do it like this? I am aliasing the column names to Date1, Date2, Date3.


WITH cte AS
(
	SELECT field1, field2, 
	ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY cast(field2 AS date)) rownum
	FROM YourTable
)
SELECT
    Field1,
	[1] AS Date1,
	[2] AS Date2,
	[3] AS Date3
FROM
	cte
PIVOT
( MAX(field2) FOR rownum IN([1],[2],[3]))P

If you have an unknown number of dates, then you would either need to have a lot of columns so the largest possible case can be taken care of, OR, use dynamic pivoting - see Madhivanan's blog here: http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

michaeleisenstein
Starting Member

4 Posts

Posted - 2011-05-11 : 09:28:10

Many thanks!! This worked perfectly! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources