[Resolved] SQL Query v9

Get help on databases - MySQL, Oracle, Access, etc.
Locked
GrimStoner
Registered User
Posts: 716
Joined: 08 Oct 2004, 02:00
Contact:

[Resolved] SQL Query v9

Post by GrimStoner »

Hi

I have the following table structure :

Code: Select all

ParentObjectID  ObjectID  Status
--------------  ---------  ------
       1             1          1
       1             2          1
       1             3          2
       1             4          2
       1             5          3
       2             6          1
I need to a query to get this resultset :

Code: Select all

ParentObjectID   Status1Count    Status2Count    Status3Count
      1                2                2                 1
      2                1                0                 0
The furthest I could get is

Code: Select all

SELECT ParentObjectID, Status, count([Status]) AS [StatusCount] FROM MyTable GROUP BY [Status], ParentObjectID
which gives me each ParentObjectID, with the Status, and the number of rows per Status, i.e. from the example this would be

Code: Select all

ParentObjectID   Status   StatusCount
       1             1            2
       1             2            2
       1             3            1
       2             1            1
Can anyone help?
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query v9

Post by RuadRauFlessa »

:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query v9

Post by RuadRauFlessa »

So in effect what you want is:

Code: Select all

declare @temptable table (colorname varchar(25),Hexa varchar(7),rgb varchar(1), rgbvalue tinyint)    
   
insert into @temptable values('Violet','#8B00FF','r',139);   
insert into @temptable values('Violet','#8B00FF','g',0);   
insert into @temptable values('Violet','#8B00FF','b',255);   
insert into @temptable values('Indigo','#4B0082','r',75);   
insert into @temptable values('Indigo','#4B0082','g',0);   
insert into @temptable values('Indigo','#4B0082','b',130);   
insert into @temptable values('Blue','#0000FF','r',0);   
insert into @temptable values('Blue','#0000FF','g',0);   
insert into @temptable values('Blue','#0000FF','b',255);   
   
select * from @temptable   
  
SELECT colorname,hexa,[r], [g], [b]   
FROM   
(SELECT colorname,hexa,rgb,rgbvalue   
    FROM @temptable) AS TableToBePivoted   
PIVOT   
(   
sum(rgbvalue)   
FOR rgb IN ([r], [g], [b])   
) AS PivotedTable;   
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query v9

Post by RuadRauFlessa »

Or something in those lines in any case.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
GrimStoner
Registered User
Posts: 716
Joined: 08 Oct 2004, 02:00
Contact:

Re: SQL Query v9

Post by GrimStoner »

Thx man...

Resulting query :

Code: Select all

SELECT ParentObjectID, SUM([1]) as [Status1], SUM([2]) as [Status2],SUM([3]) as [Status3] FROM MyTable PIVOT (COUNT(Status) FOR Status IN ([1],[2],[3],[4],[5])) p GROUP BY ParentObjectID
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

[Resolved] Re: SQL Query v9

Post by RuadRauFlessa »

:twisted:
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
Locked