Looking for an elegant way to tranpose my query result

Question

Looking for an elegant way to tranpose my query result

halifaxdal

SSCoach

Points: 19741
More actions
January 19, 2015 at 8:03 am

#294391

I have a query to generate result like this:
query:
select
row_number() over (order by @@rowcount) as ID,
HML = Case when High = 1 Then 'High' Else case when Medium = 1 Then 'Medium' else case When Low = 1 Then 'Low' End End End,
sum(case when ltrim(rtrim(deviceTypeDesc)) in
('IBM Server',
'Netware Server',
'OpenVMS Server',
'Tandem Server',
'Unix Server',
'VMWare Server',
'Windows Server',
'All BSD',
'WorldGroup Server') then 1 else 0 end) as [Server]
from z_SIVMS_currentHosts
Group by High, Medium, Low
result:
IDHMLServer
1Low8137
2Medium842
3High4782
Please note, for the sake of starting it with a simpler version, I include only 'Server' from deviceTypeDesc in the query, the actual deviceTypeDesc has more than 'Server'.
What I am looking for is a tranposed result like:
BoxTypeHighMediumLow
Server47828428137
Here is the code I came up but I will end up using multiple Union to join each single BoxType, I am looking for an elegant/efficient way to get the result:
select 'Server' as BoxType, Sum(s.High) as High, Sum(s.Medium) as Medium,Sum(s.Low) as Low From
(select
row_number() over (order by @@rowcount) as ID,
ltrim(rtrim(deviceTypeDesc)) as 'BoxType',
Sum(Case when High = 1 Then 1 Else 0 End) as High,
Sum(Case when Medium = 1 Then 1 Else 0 End) as Medium,
Sum(Case when Low = 1 Then 1 Else 0 End) as Low
from z_SIVMS_currentHosts
Group by ltrim(rtrim(deviceTypeDesc))
Having ltrim(rtrim(deviceTypeDesc)) in
('IBM Server',
'Netware Server',
'OpenVMS Server',
'Tandem Server',
'Unix Server',
'VMWare Server',
'Windows Server',
'All BSD',
'WorldGroup Server') ) s
Thank you very much.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Two methods in the following article. For performance reasons and readability (even though the code can be a bit longer), I prefer the "CROSSTAB" method.

http://www.sqlservercentral.com/articles/T-SQL/63681/

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

To add to that, you're grouping by the wrong column. You need to group by the BOX TYPE column.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

You can use PIVOT if you wish:

;WITH YourQuery

AS

( -- Put your query here:

SELECT *

FROM (VALUES (1,'Low',8137),

(2, 'Medium', 842),

(3, 'High', 4782)) D(ID,HML,Server)

)

SELECT 'Server' AS BoxType, PT.[Low], PT.[Medium], PT.[High]

FROM (SELECT HML, Server FROM YourQuery) YQ

PIVOT (MAX(Server) FOR HML IN ([Low],[Medium],[High])) AS PT

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

halifaxdal SSCoach Points: 19741 More actions · Answer 4

Jeff Moden (1/19/2015)
To add to that, you're grouping by the wrong column. You need to group by the BOX TYPE column.

I am not grouping on the wrong column, each final boxtype contains more than one occurences of individual boxtype.

I have completed my final script and I am using the same "ugly" way to do it as I think in my case it is the nature of the raw data.