Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Simular to PIVOT Table, but not Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 5:19 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
Hello Everyone
I am having a real time with this one.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#LabValues','U') IS NOT NULL
DROP TABLE #LabValues

--===== Create the test table with
CREATE TABLE #LabValues
(
UserID int,
LabName varchar(150),
ResultValue Float,
)

Sample Data

SELECT 23456789, 'White Blood Cell Count',	4.5 UNION ALL
SELECT 23456789, 'Red Blood Cell Count', 3.16 UNION ALL
SELECT 23456789, 'Hemoglobin', 11.2 UNION ALL
SELECT 23456789, 'Hematocrit', 32.1 UNION ALL
SELECT 23456789, 'Mean Corpuscular Volume', 101.6 UNION ALL
SELECT 23456789, 'Mean Corpuscular Hgb', 35.4 UNION ALL
SELECT 23456789, 'Mean Corpuscular Hgb' Conc, 34.9 UNION ALL
SELECT 23456789, 'Red Cell Distribution SD', 59.7 UNION ALL
SELECT 23456789, 'Red Cell Distribution CV', 16.1 UNION ALL
SELECT 23456789, 'Mean Platelet Volume', 9.3 UNION ALL
SELECT 23456789, 'Absolute Segmented Neutrophil', 3.51 UNION ALL
SELECT 23456789, 'Absolute Monocytes', 0.41 UNION ALL
SELECT 23456789, 'Absolute Eosinophil', 0.14 UNION ALL
SELECT 23456789, 'Absolute Basophil', 3.48 UNION ALL
SELECT 23456789, 'Absolute Neutrophil Count', 3.51 UNION ALL
SELECT 23456789, 'Manual Differential', 4.55 UNION ALL
SELECT 23456789, 'Anisocytosis', 2.34 UNION ALL
SELECT 23456789, 'Macrocytes', 1.17 UNION ALL
SELECT 23456789, 'Potassium Blood', 3.9 UNION ALL
SELECT 23456789, 'Creatinine', 0.6 UNION ALL
SELECT 23456789, 'Calcium Blood Level', 8.3 UNION ALL
SELECT 23456789, 'Total Protein Blood', 6.0 UNION ALL
SELECT 23456789, 'Bilirubin Total', 0.6 UNION ALL
SELECT 23456789, 'White Blood Cell Count', 3.1

What I am looking for is this:

UserID [White Blood Cell Count] [Red Blood Cell Count] [Hemoglobin]
23456789 4.5 3.16 11.2


etc.......

Does there have to be the same number of rows for each UserID?

Thanks in advance for your help and suggestions

Andrew SQLDBA
Post #1395362
Posted Tuesday, December 11, 2012 6:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:45 PM
Points: 3,617, Visits: 5,237
Happy to help out!

SELECT UserID
,[White Blood Cell Count]=MAX(CASE LabName WHEN 'White Blood Cell Count' THEN ResultValue END)
,[Red Blood Cell Count]=MAX(CASE LabName WHEN 'Red Blood Cell Count' THEN ResultValue END)
,[Hemoglobin]=MAX(CASE LabName WHEN 'Hemoglobin' THEN ResultValue END)
FROM #LabValues
GROUP BY UserID


The answer to your question is no.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1395378
Posted Tuesday, December 11, 2012 6:52 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 1,945, Visits: 2,863
Tables have to have keys; your deck of punch cards did not have a key and could never have key as declared. FLOAT is seldom the right data type because of rounding errors, display problems, insanely over sized precision, etc.


CREATE TABLE Lab_Tests
(user_id INTEGER NOT NULL,
lab_name VARCHAR(30) NOT NULL,
PRIMARY KEY (user_id, lab_name),
test_result DECIMAL (5,2) NOT NULL);


You also need to learn ANSI/ISO insertion syntax and to stop using 1970's Sybase dialect:

INSERT INTO Lab_Tests
VALUES
(23456789, 'White Blood Cell Count', 4.5),
(23456789, 'Red Blood Cell Count', 3.16),
(23456789, 'Hemoglobin', 11.2),
(23456789, 'Hematocrit', 32.1),
(23456789, 'Mean Corpuscular Volume', 101.6),
(23456789, 'Mean Corpuscular Hgb', 35.4),
(23456789, 'Mean Corpuscular Hgb' Conc, 34.9),
(23456789, 'Red Cell Distribution SD', 59.7),
(23456789, 'Red Cell Distribution CV', 16.1),
(23456789, 'Mean Platelet Volume', 9.3),
(23456789, 'Absolute Segmented Neutrophil', 3.51),
(23456789, 'Absolute Monocytes', 0.41),
(23456789, 'Absolute Eosinophil', 0.14),
(23456789, 'Absolute Basophil', 3.48),
(23456789, 'Absolute Neutrophil Count', 3.51),
(23456789, 'Manual Differential', 4.55),
(23456789, 'Anisocytosis', 2.34),
(23456789, 'Macrocytes', 1.17),
(23456789, 'Potassium Blood', 3.9),
(23456789, 'Creatinine', 0.6),
(23456789, 'Calcium Blood Level', 8.3),
(23456789, 'Total Protein Blood', 6.0),
(23456789, 'Bilirubin Total', 0.6),
(23456789, 'White Blood Cell Count', 3.1);

Next, SQL is a database language. It does not do display formatting as you wish to do. We get the data an throw it over the wall to a presentation layer in a C/S model of computing. A query returns a table, not a report.

Oh, good SQL programmers do no use the proprietary PIVOT kludge.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1395388
Posted Tuesday, December 11, 2012 6:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:45 PM
Points: 3,617, Visits: 5,237
CELKO (12/11/2012)


Oh, good SQL programmers do no use the proprietary PIVOT kludge.


I don't use UNPIVOT either. I use CROSS APPLY VALUES (http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/) instead. Probably not an ISO standard but what the heck if it's faster (and it usually is).



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1395392
Posted Wednesday, December 12, 2012 4:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:48 AM
Points: 1,118, Visits: 1,582
If I am not too late....here is the dynamic version :

Declare @sql Nvarchar(MAX) 
;With CTE
As
(
Select Distinct LabName From #LabValues
)
Select @sql = STUFF((Select ',MAX(Case When LabName = ' + CHAR(39) + LabName + CHAR(39) + ' Then ResultValue Else '''' End) AS ' + CHAR(39) + LabName + CHAR(39) From CTE For XML Path('')),1,1,'')
Select @sql = 'Select UserId, ' + @sql + ' From #LabValues Group By UserId'
Execute (@sql)



Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1395593
Posted Wednesday, December 12, 2012 8:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
CELKO (12/11/2012)

You also need to learn ANSI/ISO insertion syntax and to stop using 1970's Sybase dialect:


Only the first part of that is true. The second part is totally not true especially when asking for help on a forum. This problem can be solved using SQL from all the way back to at least SQL Server 6.5. Since the ANSI/ISO syntax that you're talking about isn't available in SQL Server 2005 or earlier, you'll be cutting out a lot of people who might be "stuck" on a pre-2008 box that could actually help but decided not to simply because they didn't want to take the time to convert the test setup code.


Oh, good SQL programmers do no use the proprietary PIVOT kludge.


Although I'll certainly agree that there are sometimes better methods than PIVOT, there are a lot of excellent programmers who have realized that true portability is a myth and have elected to use all of the horsepower that SQL Server has to offer, ANSI/ISO or not. You're passive-aggressive name calling doesn't help here (or anywhere) and it diminishes your esteem as a mentor and as an SME. Please, just stop it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1395701
Posted Wednesday, December 12, 2012 8:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
The thing of the data is this. I have no idea what will be in that column, so a case statement will not work, that I see.

And there are many other UserIDs in this table.

I need to make the value become a column name, and then the other value be the data in that column for that particular UserID.

Thanks
Andrew SQLDBA
Post #1395731
Posted Wednesday, December 12, 2012 10:41 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:16 AM
Points: 945, Visits: 1,769
AndrewSQLDBA (12/12/2012)
The thing of the data is this. I have no idea what will be in that column, so a case statement will not work, that I see.

And there are many other UserIDs in this table.

I need to make the value become a column name, and then the other value be the data in that column for that particular UserID.

Thanks
Andrew SQLDBA


so the above tests could be any thing? you could have some odd ball like (UserID, 'My Special Test', 97.5)? The dynamic version that Vinu posted will get close to what you want but you may want to limit it to just the userID you are looking for otherwise you will have a column for every different value in the LabName column whether it was performed or not.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1395805
Posted Wednesday, December 12, 2012 1:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
Yes, that is exactly what I need / want. I mean, there will not be 2 million different LabNames, but one userID could have 3, and another have 30 - 40. Not a huge amount.

My other question is, does each UserID have to have the same number? I would be happy if the user that has only 3, compared to one that has 20, will have null values in the missing columns.

Thanks
Andrew SQLDBA
Post #1395880
Posted Wednesday, December 12, 2012 2:10 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:16 AM
Points: 945, Visits: 1,769
AndrewSQLDBA (12/12/2012)
Yes, that is exactly what I need / want. I mean, there will not be 2 million different LabNames, but one userID could have 3, and another have 30 - 40. Not a huge amount.

My other question is, does each UserID have to have the same number? I would be happy if the user that has only 3, compared to one that has 20, will have null values in the missing columns.

Thanks
Andrew SQLDBA


No all users do not have to have the same amount. however if UserID 1 has tests A, B, and C and UserID 2 only has tests A and B the row for UserID 2 for test C will have a null unless handled by the case statement as vinu does by replacing with an empty string (which is converted to 0 since its a numeric value, in my sample i converted it to a varchar so its an empty string with no display, how you handle it will be up to you)

As far as the number of columns, Vinu's solution looks at the distinct values across the entire table for LabName. so if you have 10 Users each with 10 different tests (no overlap between the tests performed on each user) you will wind up with 100 columns.

The code below demonstrates what i mean with 2 users and 6 tests.

IF OBJECT_ID('tempdb..#LabValues') IS NOT NULL
DROP TABLE #LabValues
SELECT * INTO #LabValues FROM (VALUES (1,'A',5),(1,'B',6),(1,'C',7),(2,'D',8),(2,'E',9),(2,'F',4))x(UserID,LabName,ResultValue)


Declare @sql Nvarchar(MAX)
;With CTE
As
(
Select Distinct LabName From #LabValues
)
Select @sql = STUFF((Select ',MAX(Case When LabName = ' + CHAR(39) + LabName + CHAR(39) + ' Then CAST(ResultValue AS VARCHAR) Else '''' End) AS ' + CHAR(39) + LabName + CHAR(39) From CTE For XML Path('')),1,1,'')
Select @sql = 'Select UserId, ' + @sql + ' From #LabValues Group By UserId'
Execute (@sql)




For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1395887
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse