Table query

Question

Table query

Beginner2012

SSC-Addicted

Points: 427
More actions
May 22, 2012 at 11:01 am

#274327

Hello,
I have a the following table:
Time NumCaseCode
15h00 - 16h00141
15h00 - 16h00142
16h00 - 17h00343
17h00 - 18h00144
13h00 - 14h00245
14h00 - 15h00146
13h00 - 14h00147
Want to query the table to have:
Time Period 41424344454647
13h00 - 14h000000201
14h00 - 15h000000010
15h00 - 16h001100000
16h00 - 17h000030000
17h00 - 18h000001000
Total1131211 1
Any suggestions ?
Thank you

Viewing 14 posts - 1 through 13 (of 13 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Have you looked at Pivot and Unpivot on MSDN? It's in the From clause documentation: http://msdn.microsoft.com/en-us/library/ms177634.aspx

It looks to me like those will do what you need.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

drew.allen SSC Guru Points: 76996 More actions · Answer 2

Consider using a reporting tool, such as SSRS, to write your reports. Otherwise the crosstab/pivot already recommended is your best approach.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Beginner2012 SSC-Addicted Points: 427 More actions · Answer 3

Hello,

I'm using SSRS..Any clue how to perform such transpose in SSRS ?

Thank you

Paul White SSC Guru Points: 150468 More actions · Answer 4

The is one way to implement a pivot with totals:

-- Sample table

DECLARE @data AS TABLE

(

Code tinyint NOT NULL CHECK (Code BETWEEN 41 AND 47),

StartHour tinyint NOT NULL CHECK (StartHour BETWEEN 0 AND 23),

Cases tinyint NOT NULL CHECK (Cases >= 0),

PRIMARY KEY (Code, StartHour)

);

-- Sample data

INSERT @data

(StartHour, Cases, Code)

VALUES

(15, 1, 41),

(15, 1, 42),

(16, 3, 43),

(17, 1, 44),

(13, 2, 45),

(14, 1, 46),

(13, 1, 47);

-- Solution

SELECT

[Time Period] = ISNULL(CONVERT(nchar(5), d.StartHour), N'Total'),

[41] = SUM(CASE WHEN d.Code = 41 THEN d.Cases ELSE 0 END),

[42] = SUM(CASE WHEN d.Code = 42 THEN d.Cases ELSE 0 END),

[43] = SUM(CASE WHEN d.Code = 43 THEN d.Cases ELSE 0 END),

[44] = SUM(CASE WHEN d.Code = 44 THEN d.Cases ELSE 0 END),

[45] = SUM(CASE WHEN d.Code = 45 THEN d.Cases ELSE 0 END),

[46] = SUM(CASE WHEN d.Code = 46 THEN d.Cases ELSE 0 END),

[47] = SUM(CASE WHEN d.Code = 47 THEN d.Cases ELSE 0 END)

FROM @data AS d

GROUP BY

GROUPING SETS

(d.StartHour, ())

ORDER BY

GROUPING(StartHour),

d.StartHour;

Output:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 5

Beginner2012 (5/22/2012)
I'm using SSRS..Any clue how to perform such transpose in SSRS?

Now you tell us :laugh:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jared SSC Guru Points: 61793 More actions · Answer 6

Beginner2012 (5/22/2012)
Hello,
I'm using SSRS..Any clue how to perform such transpose in SSRS ?
Thank you

You would take the column name that you want (Code) and drag it into the Column section of the tablix. That will break out each value into a separate column.

Jared
CE - Microsoft

Beginner2012 SSC-Addicted Points: 427 More actions · Answer 7

Hello Paul,

Is it possible to make this dynamic, because I do not know how many different codes I have ?

Thank you

Paul White SSC Guru Points: 150468 More actions · Answer 8

Beginner2012 (5/22/2012)
Is it possible to make this dynamic, because I do not know how many different codes I have?

Yes, but you don't want to do it in T-SQL code, do it in SSRS as Jared says.

In future, please include important details like the fact you are working on an SSRS report in your first post.

I feel like I wasted ten minutes typing your data and providing a solution you don't need.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Beginner2012 SSC-Addicted Points: 427 More actions · Answer 9

I'm sorry for the confusion Paul...Next time will do.

Beginner2012 SSC-Addicted Points: 427 More actions · Answer 10

SQLKnowItAll (5/22/2012)
Beginner2012 (5/22/2012)
Hello,
I'm using SSRS..Any clue how to perform such transpose in SSRS ?
Thank you
You would take the column name that you want (Code) and drag it into the Column section of the tablix. That will break out each value into a separate column.

That will list the column code...

Jared SSC Guru Points: 61793 More actions · Answer 11

Beginner2012 (5/22/2012)
SQLKnowItAll (5/22/2012)
Beginner2012 (5/22/2012)
Hello,
I'm using SSRS..Any clue how to perform such transpose in SSRS ?
Thank you
You would take the column name that you want (Code) and drag it into the Column section of the tablix. That will break out each value into a separate column.
That will list the column code...

Hmm... Did you run it?

Jared
CE - Microsoft

Beginner2012 SSC-Addicted Points: 427 More actions · Answer 12

That will only list the valuse in the column vertically??6

Jared SSC Guru Points: 61793 More actions · Answer 13

It will be hard for me to describe it here in a forum. Please look at this link (http://agilebi.com/jjames/2010/09/10/how-to-pivot-data-in-ssrs/) or google matrix columns SSRS.

Jared
CE - Microsoft