December 11, 2012 at 5:19 pm
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
December 11, 2012 at 6:13 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 11, 2012 at 6:57 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 12, 2012 at 4:51 am
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)
December 12, 2012 at 8:29 am
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
Change is inevitable... Change for the better is not.
December 12, 2012 at 8:58 am
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
December 12, 2012 at 10:41 am
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
December 12, 2012 at 1:56 pm
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
December 12, 2012 at 2:10 pm
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
December 12, 2012 at 4:48 pm
CELKO (12/12/2012)
That is not how the relational model works. A table is a set whose attributes are fully known at schema creation time. Elephants do not fall out of the sky in RDBMS. More important, there is no magic to transform values into attributes, into tables.Now, once you throw the data over the wall into some other tools, their rules apply. In this case, it would be a report server of some kind. And you would be invoking their cross tabs. I have no idea why Microsoft decided to invent the term "PIVOT" instead of using crosstabs, the term used by statisticians for 300+ years and most software for 50+ years.
Report servers are great tools. They get the raw table from SQL, then sort, cross tabulate, display format, do fancy charts, etc. for dozens of reports all at once. Then when some idiot manager comes up with his personal variant, you can "mousey-click" the changes and see to it he gets his purple Old English titles (I wish I was making that up).
You can use the MS tool, Crystal Reports or (if you need heavy stats) SPSS and SAS.
SQL is a data base language and it was never meant to reporting or complex calculations. Do not cut wood with a screw driver.
Yes Joe we know this is not what RDBMSs were designed for. however sometimes we are stuck dealing with non ideal systems with limited budgets and constraints on exactly what we can do. To deal with that we break out any kludge that can get the job done so our boss will be happy. In the class room ISO standards and "Proper" locations for formatting data are a great thing to teach but in the real world when the ideal environment almost never exists we are stuck with the tools we have. If all i have is a screwdriver i can sure find a way to make it cut wood if i need to.
Im starting to join with Lowel and Lynn in my opinion of your posts. While there are small nuggets in there most of the time they dont help the actual poster and border on trolling.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
December 12, 2012 at 5:20 pm
CELKO (12/12/2012)
Elephants do not fall out of the sky in RDBMS.
Obviously you've never lived in Thailand. Here they do.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 13, 2012 at 5:29 am
CELKO (12/12/2012)
SQL is a data base language and it was never meant to reporting [font="Arial Black"]or complex calculations.[/font] Do not cut wood with a screw driver.
Heh... yeah... I know exactly what you're talking about. Take a look at what the guy in the following post is actually trying to do in an RDBMS. 😉
http://www.sqlservercentral.com/Forums/FindPost1394442.aspx
Rumor has it that he's got a Masters Degree in mathematics and yet he still tries to do such a thing in T-SQL! Talk about using screwdrivers and hammers, eh? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2012 at 5:34 am
Jeff Moden (12/13/2012)
CELKO (12/12/2012)
SQL is a data base language and it was never meant to reporting [font="Arial Black"]or complex calculations.[/font] Do not cut wood with a screw driver.Heh... yeah... I know exactly what you're talking about. Take a look at what the guy in the following post is actually trying to do in an RDBMS. 😉
http://www.sqlservercentral.com/Forums/FindPost1394442.aspx
Rumor has it that he's got a Masters Degree in mathematics and yet he still tries to do such a thing in T-SQL! Talk about using screwdrivers and hammers, eh? 😉
How is it that I knew in advance where that link was gonna take me? 😛
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 13, 2012 at 6:49 pm
dwain.c (12/13/2012)
How is it that I knew in advance where that link was gonna take me? 😛
BWAAA-HAAAA!!!! I guess I have to work on being more subtle. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply