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 2005 Forums
 Transact-SQL (2005)
 SQL 2005 Pivot Help - Rows to columns

Author  Topic 

renvilo
Starting Member

7 Posts
Posted - 2010-10-13 : 09:57:40
Hi guys,

Who knows SQL??? hehe...

I have a Q... I have a "program" that is linked to a Database (I didn't develop it so I can say it sucks)

Anyway, the data is captured in 8 Columns. For every 1 user entry (1 file that is captured) there's 16 Rows. EG:




Column:
SourceID | VisitID | Query | Response


BLA | 1123 | Gender | 1
BLA | 1123 | ClientNr | 1576
BLA | 1123 | RefTo | 01
BLA | 1123 | Status | 03


and so on...

I need the data like this:


Colum:
SourceID | Gender | ClientNr | RefTo | Status

BLA _____| 1_____| 1576 ___| 01____| 03



This is my SQL code:


SELECT VisitID,Gender,"Client Code:"
FROM (
SELECT VisitID,Query
From PrvEncounterQueries) AS up
PIVOT (Count(Query) For Query IN (Gender,"Client Code:")) As PivotTable


It works fine but the problem is that "count" part...

What I get at the moment is:

Column:
SourceID | Gender | Client Code

BLA ______| 2 _____| 01



So it works but it counts... Sum doesn't work, Min and Max is useless... I need to show the value. Is that possible?

Tx

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2010-10-13 : 10:42:43
Is this what you're looking for?

declare @t table (SourceID varchar(10), VisitID int, Query varchar(20), Response varchar(10))
insert @t
select 'BLA', 1123, 'Gender', '1' union all
select 'BLA', 1123, 'ClientNr', '1576' union all
select 'BLA', 1123, 'RefTo', '01' union all
select 'BLA', 1123, 'Status', '03' union all
select 'BLA', 1124, 'Gender', 'male' union all
select 'BLA', 1124, 'ClientNr', '9999' union all
select 'BLA', 1124, 'RefTo', 'Me' union all
select 'BLA', 1124, 'Status', 'Active'

SELECT SourceID
       ,VisitID
       ,Gender
       ,ClientNr
       ,RefTo
       ,[Status]
FROM (
SELECT SourceID, VisitID,Query, Response
From @t) AS up
PIVOT (max(Response) For Query IN (Gender,[ClientNr],RefTo,[Status])) As PivotTable

OUTPUT:
SourceID   VisitID     Gender     ClientNr   RefTo      Status
---------- ----------- ---------- ---------- ---------- ----------
BLA        1123        1          1576       01         03
BLA        1124        male       9999       Me         Active


Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2010-10-13 : 10:46:24
This example doesn't need the derived table:

SELECT SourceID
       ,VisitID
       ,Gender
       ,ClientNr
       ,RefTo
       ,[Status]
FROM @t --PrvEncounterQueries
PIVOT (
       max(Response) 
       For Query IN (Gender,ClientNr,RefTo,[Status])
       ) As PivotTable


Be One with the Optimizer
TG
Go to Top of Page

renvilo
Starting Member

7 Posts
Posted - 2010-10-13 : 10:51:36
Hi man,

I hope so lol.. I'll test it tomorrow morning because I'm at home now (I'm in South Africa so...)

But thanks. Hope it works
Go to Top of Page

renvilo
Starting Member

7 Posts
Posted - 2010-10-14 : 03:16:23
Hi TG,

Cool looks like it's working :)

Just waiting for the guys to change the compatibility level to 90 :)
Go to Top of Page

renvilo
Starting Member

7 Posts
Posted - 2010-10-14 : 06:45:55
Dam... Ok TG not not working...

It works if I want to get 1 Record right...

If I say top 10 and I say Where VisitID like '%xxxxxx%' then it works... But once I remove the Top 10 and like statement then it brings back all the VisitID and the other columns are all Null????
Go to Top of Page

renvilo
Starting Member

7 Posts
Posted - 2010-10-14 : 06:57:54
This is my code and it's working good except it's not working :) :

SELECT SourceID
,VisitID
,"Client Code:"
,"Gender:"
,"Year of birth:"
,"Eligibility Status"
,"Final result given to client:"
FROM (
SELECT SourceID, VisitID,Query, Response
From PrvEncounterQueries) AS up
PIVOT (MAX(Response) For Query IN ([Client Code:],[Gender:],[Year of birth:],[Eligibility Status],[Final result given to client:],RefTo,[Status])) As PivotTable
where VisitID like 'V1002900877'


It's working but the problem is it's only giving me the VisitID with the value 'V1002900877'

There's thousands of records and if I remove the Where clause then the VisitID brings all the info but all the other columns are 'Null'


Also will I be able to link this to another table to get a service date?
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2010-10-14 : 10:28:33
Sounds like you need to limit the data to just the values in your PIVOT columns. ie:
WHERE QUERY in ('Client Code:', 'Gender', 'Year of birth:', 'Eligibility Status','Final result given to client:','RefTo','Status')
All you Nulls are probably due to values other than this list.

If that doesn't do it then please mock up some sample code as I did above to illustrate the problem you're having.
Then post the expected results based on your sample data.
Make sure your sample data is executable so I don't need to type it all out myself.

Be One with the Optimizer
TG
Go to Top of Page

dmilam
Posting Yak Master

185 Posts
Posted - 2010-10-15 : 15:15:36
Regarding the comaptibility level issue, try using tempdb to test the code.
Go to Top of Page

renvilo
Starting Member

7 Posts
Posted - 2010-10-17 : 06:44:50
Hi guys,

regarding the code... I'll up it on Monday. I got it finished thanks to an other forum post :)

The compatibility level has been changed so... But I'll post the answer :D

tx
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site