How to display data in column as different columns

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

Transact-SQL (2008)

How to display data in column as different columns

Author

Topic

harry123
Starting Member

13 Posts

Posted - 2011-08-01 : 11:59:58

I have to display the data in a single column as different columns.

data looks like:

PatientID DoctorID Datetreated
--------- -------- -----------
123 345 06/08/2010
123 249 07/09/2010
123 287 06/21/2010
125 230 08/08/2010
125 187 09/01/2010

The (DoctorID, Datetreated) should be repeated for 7 times.
if there is no data,it should just display the pipes.
The output format should be:
PatientID|DoctorID|DateTreated|DoctorID|DateTreated|DoctorID|DateTreated|DoctorID|DateTreated|DoctorID|DateTreated|DoctorID|DateTreated|DoctorID|DateTreated|

Desired Output should be:

123|345|06/08/2010|249|07/09/2010|287|06/21/2010|||||||||
125|230|08/08/2010|187|09/01/2010|||||||||||

Can someone help me out on this?. I would really appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 13:20:00

[code]
;With Number_Tally(N)
AS
(SELECT 1
UNION ALL
SELECT N +1
FROM Number_tally
WHERE N<=6
),
Patient_Det(rn,PatientID, DoctorID, Datetreated)
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY Datetreated) AS rn,*
FROM Table
),
Patient_Whole_Det (PatientID,N, DoctorID, Datetreated)
AS
(
SELECT p.PatientID,p.N,q.DoctorID,q.Datetreated
FROM
(
SELECT t.PatientID,N
FROM (SELECT DISTINCT PatientID FROM Patient_Det) t
CROSS JOIN (SELECT N FROM Number_Tally)n
)p
LEFT JOIN Patient_Det q
ON q.PatientID = p.PatientID
AND q.rn = p.N
)

SELECT CAST(p.PatientID AS varchar(20)) + (SELECT '|' + COALESCE(CAST(DoctorID AS varchar(20)),'') + '|' + COALESCE(CONVERT(varchar(10),datetreated,101),'') FROM Patient_Whole_det WHERE PatientID = p.PatientID ORDER BY N FOR XML PATH(''))
FROM (SELECT DISTINCT PatientID FROM Patient_Whole_Det) p

Output
----------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
123|345|06/08/2010|287|06/21/2010|249|07/09/2010||||||||
125|230|08/08/2010|187|09/01/2010||||||||||

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 13:24:01

if you want them in separate columns rather than as a | delimited string, use like


SELECT PatientID,
MAX(CASE WHEN  Rn =1 THEN DoctorID ELSE NULL END) AS Doctor1,
MAX(CASE WHEN  Rn =1 THEN DateTreated ELSE NULL END) AS DateTreated1,
MAX(CASE WHEN  Rn =2 THEN DoctorID ELSE NULL END) AS Doctor2,
MAX(CASE WHEN  Rn =2 THEN DateTreated ELSE NULL END) AS DateTreated2,
...
MAX(CASE WHEN  Rn =7 THEN DoctorID ELSE NULL END) AS Doctor7,
MAX(CASE WHEN  Rn =7 THEN DateTreated ELSE NULL END) AS DateTreated7
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY DateTreated ) AS Rn,*
FROM Table
)t
GROUP BY PatientID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harry123
Starting Member

13 Posts

Posted - 2011-08-01 : 15:13:58

Thanks Visakh for the info.
The PatientID is in One column and the rest in One Column.
I didn't quite get that because i am a beginner. If you don't mind, please can u explain it.

harry123
Starting Member

13 Posts

Posted - 2011-08-01 : 16:37:10

The PatientID, DoctorID, DateTreated are in 3 different tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 01:01:07

quote:
Originally posted by harry123

The PatientID, DoctorID, DateTreated are in 3 different tables.

show your table structures without which i cant make out how data is present.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harry123
Starting Member

13 Posts

Posted - 2011-08-06 : 22:37:51

My Requirement is to generate a query which uses 4 tables:
1.PATIENT
2.ENC
3.ACCOUNT
4.CL_DOC
I should write a query to display:

SELECT PAT.PAT_ID,PAT.LAST_NAME,PAT.FIRST_NAME,HSP.ENC_ID,ACCT.ATTENDINGDOC_ID,DOC.DOC_NAME FROM
ENC HSP INNER JOIN ACCOUNT ACCT ON HSP.ENC_ID=ACCT.ENC_ID
INNER JOIN PATIENT PAT ON HSP.PAT_ID=PAT.PAT_ID
INNER JOIN CL_DOC DOC ON ACCT.ATTENDINGDOC_ID=DOC.DOC_ID .

The Columns ACCT.ATTENDINGDOC_ID, DOC.DOC_NAME should be repeated 4 times. Each patient may be treated by 1 or more attending doctors. But we are only bothered only about the first 4 doctors who treated the patient. If the number of doctors treating a patient are less than 4 then we should display the attending doctors available and rest of them with empty spaces. Suppose if there is only 1 attending doctor for a particular patient then display like 123|john|||||||.

I am finding difficulty in repeating the columns within a SELECT query?
Can you please help me out?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 04:33:34

[code]
SELECT PAT.PAT_ID,PAT.LAST_NAME,PAT.FIRST_NAME,
MAX(CASE WHEN Rn =1 THEN ACCT.ATTENDINGDOC_ID ELSE NULL END) AS DocID1,
MAX(CASE WHEN Rn =1 THEN DOC.DOC_NAME ELSE NULL END) AS DocName1,
MAX(CASE WHEN Rn =2 THEN ACCT.ATTENDINGDOC_ID ELSE NULL END) AS DocID2,
MAX(CASE WHEN Rn =2 THEN DOC.DOC_NAME ELSE NULL END) AS DocName2,
MAX(CASE WHEN Rn =3 THEN ACCT.ATTENDINGDOC_ID ELSE NULL END) AS DocID3,
MAX(CASE WHEN Rn =3 THEN DOC.DOC_NAME ELSE NULL END) AS DocName3,
MAX(CASE WHEN Rn =4 THEN ACCT.ATTENDINGDOC_ID ELSE NULL END) AS DocID4,
MAX(CASE WHEN Rn =4 THEN DOC.DOC_NAME ELSE NULL END) AS DocName4
FROM
(

SELECT ROW_NUMBER() OVER (PARTITION BY PAT.PAT_ID ORDER BY ACCT.ATTENDINGDOC_ID ) AS Rn,PAT.PAT_ID,PAT.LAST_NAME,PAT.FIRST_NAME,HSP.ENC_ID,ACCT.ATTENDINGDOC_ID,DOC.DOC_NAME
FROM
ENC HSP INNER JOIN ACCOUNT ACCT ON HSP.ENC_ID=ACCT.ENC_ID
INNER JOIN PATIENT PAT ON HSP.PAT_ID=PAT.PAT_ID
INNER JOIN CL_DOC DOC ON ACCT.ATTENDINGDOC_ID=DOC.DOC_ID
)t
GROUP BY PAT.PAT_ID,PAT.LAST_NAME,PAT.FIRST_NAME

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harry123
Starting Member

13 Posts

Posted - 2011-08-10 : 15:11:22

But i have to use this query in SSIS. So when i tried, it says the OVER is not supported in SSIS. Is there any alternate way ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 08:29:22

OVER is supported. which task you're using this in SSIS?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harry123
Starting Member

13 Posts

Posted - 2011-08-11 : 10:55:56

Data flow task.
when i connected to the OLEDB source with data access mode as SQL COMMAND and tried building the query. It says OVER is not supported.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 11:12:42

is your databse on sql 2005 or above? is compatibility level over 90?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harry123
Starting Member

13 Posts

Posted - 2011-08-11 : 13:39:32

The version is Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel IA-64) Apr 23 2011 00:40:16 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 <IA-64> (Build 3790: Service Pack 2)
Compatibility level is 100

quote:
Originally posted by visakh16

is your databse on sql 2005 or above? is compatibility level over 90?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 13:48:51

check it

exec sp_dbcmptlevel 'dbname'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harry123
Starting Member

13 Posts

Posted - 2011-08-11 : 13:51:49

Compatibility level is 100

The version is Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel IA-64) Apr 23 2011 00:40:16 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 <IA-64> (Build 3790: Service Pack 2)
Compatibility level is 100

quote:
Originally posted by visakh16

check it

exec sp_dbcmptlevel 'dbname'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-12 : 02:08:01

then it should support OVER()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harry123
Starting Member

13 Posts

Posted - 2011-08-12 : 10:42:43

I have executed it in SQL server. It was executed perfectly. But when i used it in SSIS(BIDS) .
Then it is showing error like "THE OVER IS NOT SUPPORTED". Is there anything that i need to change?

quote:
Originally posted by visakh16

then it should support OVER()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources