SQL 2005 Pivot Help - Rows to Column

Question

Post reply

SQL 2005 Pivot Help - Rows to Column

renvilo

Mr or Mrs. 500

Points: 522
More actions
October 13, 2010 at 8:03 am

#147282

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:
Colum:
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:
Column:
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

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I don't know if this is exactly what you're after, but should do the trick:

DECLARE @pivotSource TABLE (

SourceID char(3),

VisitID int,

Query varchar(10),

Response nvarchar(255)

)

INSERT INTO @pivotSource

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

SELECT *

FROM @pivotSource AS up

PIVOT (MIN(Response) For Query IN ( Gender , ClientNr , RefTo , Status)) As PivotTable

The main thing to worry about is that this DB design looks a lot like EAV (Entity Attribute Value).

Be warned that it's not a relational design and leads to this kind of oddnesses to deal with.

Hope this helps

Gianluca

-- Gianluca Sartori

renvilo Mr or Mrs. 500 Points: 522 More actions · Answer 2

Yes I also think the design SUCKS big time... But for a database with over 10 000 tables I think they started to panic lol...

I'll test it tomorrow morning because I just left my office.

tx

WayneS SSC Guru Points: 95461 More actions · Answer 3

The big problem with Gianluca's query is that if you add a new "Query" to the table, this won't reflect it.

You might want read the "Cross-Tabs and Pivot Tables, Part 1" and "Part 2" articles, linked to in my signature. The Part 2 discusses doing this dynamically, which you would need to handle the scenario I mention above.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

renvilo Mr or Mrs. 500 Points: 522 More actions · Answer 4

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?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Read the articles Wayne recommended. You will gain a comprehensive understanding of how this works:

DROP TABLE #Sample

CREATE TABLE #Sample (SourceID CHAR(3), VisitID INT, Query VARCHAR(10), Response VARCHAR(4))

INSERT INTO #Sample (SourceID, VisitID, Query, Response)

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'

SELECT

SourceID,

VisitID,

'Gender' = MAX(CASE WHEN Query = 'Gender' THEN Response ELSE NULL END),

'ClientNr' = MAX(CASE WHEN Query = 'ClientNr' THEN Response ELSE NULL END),

'RefTo' = MAX(CASE WHEN Query = 'RefTo' THEN Response ELSE NULL END),

'Status' = MAX(CASE WHEN Query = 'Status' THEN Response ELSE NULL END)

FROM #Sample

GROUP BY SourceID, VisitID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

renvilo Mr or Mrs. 500 Points: 522 More actions · Answer 6

Hi Chris,

I am busy with the article now 🙂 Just like to get more info coz they would like the report before tomorrow (and they asked me yesterday.. nice :))

Anyway,

I tried your code but not working. Remember the database is there, I can't do anything to it. I can only use SQL Command and Crystal. I asked them now to just give me access to stored procedures as well...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

renvilo (10/14/2010)
...I tried your code but not working...

What's the error message?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

renvilo Mr or Mrs. 500 Points: 522 More actions · Answer 8

No there's no error message 🙂 So it's working but not working lol

This is it:

SELECT top 50

VisitID,

'Gender:' = MAX(CASE WHEN Query = 'Gender:' THEN Response ELSE NULL END)

FROM PrvEncounterQueries

--where VisitID like 'V1002900877' or VisitID like 'D1002653441'

GROUP BY SourceID, VisitID

I only need to get 1 column right then the rest will follow. Reason why I only have gender in. Problem is if I put in that Where clause it does give me the right info... But I can't leave it in. So when I take it out it brings back the data:

VisitID | Gender:

1235 | NULL

4235 | NULL

4352 | NULL

2355 | NULL

4879 | NULL

If the where clause was in the info is:

VisitID | Gender:

V1002900877 | 1

D1002653441 | 1

That's the only problem now.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

I don't see a problem.

-- set up some sample data

DROP TABLE #Sample

CREATE TABLE #Sample (SourceID CHAR(3), VisitID VARCHAR(15), Query VARCHAR(10), Response VARCHAR(4))

INSERT INTO #Sample (SourceID, VisitID, Query, Response)

SELECT 'BLA', 'V1002900877', 'Gender', '1' UNION ALL

SELECT 'BLA', 'V1002900877', 'ClientNr', '1576' UNION ALL

SELECT 'BLA', 'V1002900877', 'RefTo', '01' UNION ALL

SELECT 'BLA', 'V1002900877', 'Status', '03' UNION ALL

SELECT 'BLA', 'D1002653441', 'Gender', '2' UNION ALL

SELECT 'BLA', 'D1002653441', 'ClientNr', '1577' UNION ALL

SELECT 'BLA', 'D1002653441', 'RefTo', '001' UNION ALL

SELECT 'BLA', 'D1002653441', 'Status', '003'

-- show rows to columns query

SELECT

SourceID,

VisitID,

'Gender' = MAX(CASE WHEN Query = 'Gender' THEN Response ELSE '' END),

'ClientNr' = MAX(CASE WHEN Query = 'ClientNr' THEN Response ELSE '' END),

'RefTo' = MAX(CASE WHEN Query = 'RefTo' THEN Response ELSE '' END),

'Status' = MAX(CASE WHEN Query = 'Status' THEN Response ELSE '' END)

FROM #Sample

WHERE VisitID IN ('V1002900877', 'D1002653441')

GROUP BY SourceID, VisitID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

renvilo Mr or Mrs. 500 Points: 522 More actions · Answer 10

Something is strange 🙁

I test your code and works 100%

This is what I have:

SELECT top 1000

SourceID,

VisitID,

Query,

Response,

'Gender' = MAX(CASE WHEN Query = 'Gender:' THEN Response ELSE '' END)

FROM PrvEncounterQueries

WHERE VisitID IN ('V1002900877', 'D1002653441')

GROUP BY SourceID, VisitID, Query, Response

Source ID | VisitID | Query |Response | Gender

AHS |D1002653441|Department where Employee works__|9________|

AHS |D1002653441|Eligibility Status__________________| 1 ________|

AHS |D1002653441|Final result given to client:_______| 02________|

AHS |D1002653441|Gender:_________________________| 1 ________| 1

So it actually gives me all the blank rows in between. I don't know why the database does this but If I create that #Sample one from you it works??

So it's almost there but how do I get rid of the other rows...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

renvilo (10/14/2010)
Something is strange 🙁
I test your code and works 100%
This is what I have:
SELECT top 1000
SourceID,
VisitID,
Query,
Response,
'Gender' = MAX(CASE WHEN Query = 'Gender:' THEN Response ELSE '' END)
FROM PrvEncounterQueries
WHERE VisitID IN ('V1002900877', 'D1002653441')
GROUP BY SourceID, VisitID, Query, Response
Source ID | VisitID | Query |Response | Gender
AHS |D1002653441|Department where Employee works: |9 |
AHS |D1002653441|Eligibility Status | 1 |
AHS |D1002653441|Final result given to client: | 02 |
AHS |D1002653441|Gender: | 1 | 1
So it actually gives me all the blank rows in between. I don't know why the database does this but If I create that #Sample one from you it works??

Take Query and Response out of your GROUP BY and see what happens...

EDIT: ...and your SELECT, or you will get an error

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

renvilo Mr or Mrs. 500 Points: 522 More actions · Answer 12

Ok that fixed part of it...

But ok I think I know what the problem is.... It's working BUT

This is the data I get now:

AHSD1001068962NULLNULLNULLNULL

AHSV1002920389275211978102

AHSF578860NULLNULLNULLNULL

AHSV1000908733NULLNULLNULLNULL

So you see there's a lot of NULL's

BUT this table is for Custom Defined Screens (That's why it's such a sht design) So at the moment we did a VCT Screen that captures the VCT information BUT there's other data in there as well that has Nothing to do with VCT. like:

The VisitID: D1001068962 that has NULL's has these fields that it captures:

AHSD1001068962357266PRV.ANULLALLERGIES :U

AHSD1001068962357266PRV.ACCNULLDATE OF ACCIDENT (if applicable):NULL

AHSD1001068962357266PRV.ATTNULLATTENDED TIME :NULL

AHSD1001068962357266PRV.BPNULLB/P :NULL

AHSD1001068962357266PRV.CNULL --- CONVALESCENT LEAVE ---NULL

So that has nothing to do with our VCT info. Am I right?

If so can we get them out of the way or not?

Say only if All the fields (like VisitID, ClientCode, Gender........) has info to keep them else don't show the other data?? Something like that?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

renvilo (10/14/2010)
...If so can we get them out of the way or not?...

Try filtering them out in your WHERE clause. Something like...

WHERE query NOT IN ('Unwanted value', 'Another unwanted value')

Alternatively, filter for the values you want:

WHERE Query IN ('Gender', 'ClientNr', 'RefTo', 'Status')

AND VisitID IN ('V1002900877', 'D1002653441')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

renvilo Mr or Mrs. 500 Points: 522 More actions · Answer 14

Cool.. Tx Chris.

Working fine 🙂 Now just to get them into a Store Proc and then Crystal... But waiting for them to give me access... The rest I should be able to do 😀

I owe you big time 😀