Sql query to get a desired format of display

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sql query to get a desired format of display

Author

Topic

SKP
Starting Member

34 Posts

Posted - 2008-05-23 : 11:32:51

Hi Guys,

Below is example of the current structure of table1 I have at run time:

------------------------
GroupName Resourcename Week1 Week2 ..cont.. dynamically

Associates A1 0 80 ......
Assocaites A2 20 40 ......
Associates A3 50 100 ......
Principal P1 20 100 ......
Principal P2 0 0 ......
Principal P3 0 100 ......
------------------------
I want to change the above to something like below table2:
---------------------------
GroupName Status Week1 Week2 ....cont

Associates Assigned 2 3
Associates NotAssigned 1 0
Principal Assigned 1 1
Principal NotAssigned 2 1
---------------------------

I will try to explain how I am deriving table2 from table1. I have to count the number of Resource name against each Groupname for a particular week column(Weeki i 1 to n dynamic) where value of Week column is 0, then use this numbber against NotAssigned and the complementary number to be stored as Assigned.

The table formaating is lost in HTML view but just consider any gaps between fields as next column value.

Am I clear in what i am asking , if not please ask me.

Any help will be highly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 13:05:50

You can get a start on this using below query:-

SELECT GroupName,
SUM(CASE WHEN Week1 <>0 THEN 1 ELSE 0 END) AS W1Assigned,
SUM(CASE WHEN Week1 =0 THEN 1 ELSE 0 END) AS W1Unassigned,
....
FROM table1
GROUP BY GroupName

how will you be getting the dynamic values?

SKP
Starting Member

34 Posts

Posted - 2008-05-23 : 16:00:43

Hi Visakh,

Thank you for your initial guidance, it was immenseley helpful.

With regards to your inquisition about how to handle dynamic columns, i am doing it via dynamic sql, creating the entire string using Cursor and then executing it. example is below:

Declare @sqlAssigned nvarchar(MAX)
Select @SqlAssigned = ''
Select @sqlAssigned = 'Select Classname' +', '+ ''''+'Assigned'+'''' + ' AS ' + ''''+'Availability'+'''' + ', '
print @SqlAssigned
Declare @Week varchar(10)
DECLARE Weeks CURSOR FOR
SELECT DISTINCT WeekNo FROM chartbl_StageBookings Order By WeekNo

OPEN Weeks

FETCH NEXT FROM Weeks
INTO @Week

WHILE @@FETCH_STATUS = 0
Begin
IF @Week <> (Select Max(WeekNo) from chartbl_StageBookings)
Begin
Select @sqlAssigned = @sqlAssigned + 'SUM'+'('+ 'Case WHEN ' + 'Week' + @Week + '<>' + '0 ' + 'THEN ' + '1 ' + 'ELSE '+'0 '+ 'END'+')' + 'AS ' + 'Week'+@Week + ', '
END
ELSE
BEGIN
Select @sqlAssigned = @sqlAssigned + 'SUM'+'('+ 'Case WHEN ' + 'Week' + @Week + '<>' + '0 ' + 'THEN ' + '1 ' + 'ELSE '+'0 '+ 'END'+')' + 'AS ' + 'Week'+@Week +' FROM chartbl_ResourceClassAndResource GROUP BY Classname'
END
print @sqlAssigned
FETCH NEXT FROM Weeks
INTO @Week
End

CLOSE Weeks
DEALLOCATE Weeks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources