Possible?: Use a pivot table within a select stmnt

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)

Possible?: Use a pivot table within a select stmnt

Author

Topic

joebuzz83
Starting Member

16 Posts

Posted - 2011-12-27 : 16:33:45

Hi all,

After days of searching the internet for the answer to this question, I've decided to land here and ask you fine people for assistance. I have a report that joins 10-15 tables and calls on 27 columns from those tables. I need to extract the data from one two columns from one table, concatenate them and then list the results as a pivot table within the report. Ex:

Insurance Name | Insurance ID |
Health1 | 1234
Health1 | 2222
Health2 | 3456

I need it display like this:

Health1 | Health2
Health1, 1234 | NULL
Health1, 2222 | NULL
NULL | Health2, 3456
Health1, 6789 | Health2, 9999

Please let me know if you can assist with this. Thanks.

joebuzz83
Starting Member

16 Posts

Posted - 2011-12-27 : 16:36:53

The formatting keeps messing up, but im sure you can see the structure I'm going for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 00:43:32

On what basis you group them together?
for example, how did you determine that Health1, 6789 and Health2, 9999 should end up in same row?

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

joebuzz83
Starting Member

16 Posts

Posted - 2011-12-28 : 08:56:08

visakh,

The table that holds the insurance information was joined using a unique ID. The insrance table holds the patients id, insurance name and insurance id. If a patient has more than one insurance, its attached to that patients id.

joebuzz83
Starting Member

16 Posts

Posted - 2011-12-28 : 11:44:25

I'd like to put my question another way.

I'd like to get all insurance names and ids for a specific patient and add them all into one column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 11:46:05

quote:
Originally posted by joebuzz83

visakh,

The table that holds the insurance information was joined using a unique ID. The insrance table holds the patients id, insurance name and insurance id. If a patient has more than one insurance, its attached to that patients id.

how do you think we could have realised that from the data you posted
Please give us full picture if you want accurate solutions. Unless you do that I doubt whether we will be able to help you in your problems.


SELECT Patientsid,
MAX(Health1) AS Health1,
MAX(Health2) AS Health2
FROM
(
SELECT Patientsid,InsuranceName + ',' + CAST(InsuranceID AS varchar(20)) AS Health1,CAST(NULL AS varchar(1000)) AS Health2
FROM Table
WHERE InsuranceName ='Health1'
UNION ALL
SELECT Patientsid,NULL,InsuranceName + ',' + CAST(InsuranceID AS varchar(20)) AS Health2
FROM Table
WHERE InsuranceName ='Health2'
)t
GROUP BY Patientsid

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

joebuzz83
Starting Member

16 Posts

Posted - 2011-12-28 : 12:02:05

How about instead of having the information in seperate columns, is it possible to have all of the information show in one column?

ex:

health1 - 1234, health2 - 5678

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 12:08:27

quote:
Originally posted by joebuzz83

How about instead of having the information in seperate columns, is it possible to have all of the information show in one column?

ex:

health1 - 1234, health2 - 5678

why are you keeping on changing your requirement
can i assume this is finally what you want?


SELECT t.PatientID,
COALESCE(Health1,'NULL') + '|' + COALESCE(Health2,'NULL') AS [Health1|Health2]
FROM (SELECT DISTINCT PatientID FROM Table) t
OUTER APPLY (SELECT InsuranceName + ',' + CAST(InsuranceID AS varchar(20)) AS Health1
             FROM Table
             WHERE PatientID = t.PatientID
             AND [Insurance Name]='Health1'
             )t1
OUTER APPLY (SELECT InsuranceName + ',' + CAST(InsuranceID AS varchar(20)) AS Health2
             FROM Table
             WHERE PatientID = t.PatientID
             AND [Insurance Name]='Health2'
             )t2

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources