Viewing data from multiple rows in a single row 1

[rob11nj]

Hi all.. I'm using MS SQL 2005 and am trying to take data that is in multiple rows and display it in a single row. I think my question can best be understood with an example. Here is what I have

Name Type Count
------- --------- -------
Host1 A 53
Host1 B 42
Host1 C 5
Host2 A 5
Host3 A 6
Host3 C 2
...

The "Type" will always be either A, B, or C. And I'd like to be able to get the data to come out like this via a view:

Name Type A Type B Type C
------ -------- ------ ------
Host1 53 42 5
Host2 5 NULL NULL
Host3 6 NULL 2

Anyone have any ideas of a view that would accomplish this??

Thanks!

 

[gmmastros]

Like this...

Declare @Temp Table(Name VarChar(20), Type VarChar(20), Count Integer)

Insert Into @Temp Values('Host1','A',53)
Insert Into @Temp Values('Host1','B',42)
Insert Into @Temp Values('Host1','C',5)
Insert Into @Temp Values('Host2','A',5)
Insert Into @Temp Values('Host3','A',6)
Insert Into @Temp Values('Host3','C',2)

Select Name,
       Min(Case When Type='A' Then Count End) As [Type A],
       Min(Case When Type='B' Then Count End) As [Type B],
       Min(Case When Type='C' Then Count End) As [Type C]
From   @Temp
Group By Name

The @Temp stuff is just so I could dummy up some data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rob11nj]

Exactly what I needed. Thanks!