PCFormat Forum Archive

Code: Select all

ParentObjectID  ObjectID  Status
--------------  ---------  ------
       1             1          1
       1             2          1
       1             3          2
       1             4          2
       1             5          3
       2             6          1

Code: Select all

ParentObjectID   Status1Count    Status2Count    Status3Count
      1                2                2                 1
      2                1                0                 0

Code: Select all

SELECT ParentObjectID, Status, count([Status]) AS [StatusCount] FROM MyTable GROUP BY [Status], ParentObjectID

Code: Select all

ParentObjectID   Status   StatusCount
       1             1            2
       1             2            2
       1             3            1
       2             1            1

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;   

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