Selecting data in a grid?

Question

Post reply

Selecting data in a grid?

Noffer

Ten Centuries

Points: 1090
More actions
March 27, 2008 at 4:16 pm

#379438

Is there a way that I can select data from a table and return it as a grid? For example, If I have a normalized table like this:
Month Category Value
1 1 10
1 2 10
2 1 10
2 2 10
And display it like this...
Month 1 Month 2
Category 1 10 10
Category 2 10 10
I know this sounds like a job for reporting services or cystal reports, but its for an edit interface. I know that I can just manipulate the data after its returned, but thought that maybe SQL could do it faster since I'd have to iterate across the rows.

Viewing 9 posts - 1 through 8 (of 8 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Check out the PIVOT statement in Books Online.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 2

I've been playing around with it trying to get grasp on PIVOT as well:

create table #temp

(month int,

category int,

value int)

insert into #temp

select 1,1,10 union

select 2,2,5 union

select 2,1,10 union

select 3,2,15 union

select 4,1,10 union

select 5,3,20 union

select 6,1,10 union

select 7,3,40 union

select 7,1,10 union

select 7,2,5 union

select 8,3,10 union

select 9,3,15 union

select 9,2,10 union

select 10,1,20 union

select 11,1,10 union

select 12,3,40

SELECT

CATEGORY,

ISNULL(JAN,0)JAN,

ISNULL(FEB,0)FEB,

ISNULL(MAR,0)MAR,

ISNULL(APR,0)APR,

ISNULL(MAY,0)MAY,

ISNULL(JUN,0)JUN,

ISNULL(JUL,0)JUL,

ISNULL(AUG,0)AUG,

ISNULL(SEP,0)SEP,

ISNULL(OCT,0)OCT,

ISNULL(NOV,0)NOV,

ISNULL([DEC],0)[DEC]

FROM

(SELECT CASE

WHEN MONTH = 1 THEN 'JAN'

WHEN MONTH = 2 THEN 'FEB'

WHEN MONTH = 3 THEN 'MAR'

WHEN MONTH = 4 THEN 'APR'

WHEN MONTH = 5 THEN 'MAY'

WHEN MONTH = 6 THEN 'JUN'

WHEN MONTH = 7 THEN 'JUL'

WHEN MONTH = 8 THEN 'AUG'

WHEN MONTH = 9 THEN 'SEP'

WHEN MONTH = 10 THEN 'OCT'

WHEN MONTH = 11 THEN 'NOV'

WHEN MONTH = 12 THEN 'DEC'

END AS MONTH,

CATEGORY, VALUE

FROM #TEMP) A

PIVOT

(SUM(VALUE)

FOR MONTH IN

( [JAN],[FEB],[MAR],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC])) B

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

Noffer Ten Centuries Points: 1090 More actions · Answer 3

Thanks, thats the operator that I couldn't recall. I was trying Cube, which wasn't getting me there, but I knew there was a way!

Noffer Ten Centuries Points: 1090 More actions · Answer 4

Here's what I came up with....

SELECT [Pentile],

ISNULL([1],0)'Jan',

ISNULL([2],0)'Feb',

ISNULL([3],0)'Mar',

ISNULL([4],0)'Apr',

ISNULL([5],0)'May',

ISNULL([6],0)'Jun',

ISNULL([7],0)'Jul',

ISNULL([8],0)'Aug',

ISNULL([9],0)'Sep',

ISNULL([10],0)'Oct',

ISNULL([11],0)'Nov',

ISNULL([12],0)'Dec'

FROM (SELECT [MonthNr], [Pentile], [DetailAmt] FROM CyclePentileDetail_tb

WHERE DetailType = @SegmentID AND SegmentID = @DetailType) A

PIVOT (SUM([DetailAmt])

FOR [MonthNr] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) B

ORDER BY [Pentile]

Based on how I've key'd the table, it should be a real slick way to retrieve the data for edits in the UI.

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Thanks for posting your final solution. It helps those who may come accross this thread months from now see what ended up working for you.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

I'm in the process of writing a couple of articles on Cross Tabs and Pivots... the more I dig into Pivots, the better I like the ol' classic crosstab. I won't get into when a cross tab will beat the pants off a pivot performance wise, but take a look a how easy the cross tab code is to read. And, add a row total to a pivot and see how much more messy the code becomes with absolutely no gain in performance over the cross tab.

Here's the cross tab solution for the problem in the original post... hat's off to Todd Carrier for having the stripes to build some test data...

[font="Courier New"]--===== Create and populate a test table

-- THIS IS NOT PART OF THE SOLUTION

CREATE TABLE #Temp

(

Month INT,

Category INT,

Value INT

)

INSERT INTO #Temp (Month,Category,Value)

SELECT 1,1,10 UNION ALL

SELECT 2,2,5 UNION ALL

SELECT 2,1,10 UNION ALL

SELECT 3,2,15 UNION ALL

SELECT 4,1,10 UNION ALL

SELECT 5,3,20 UNION ALL

SELECT 6,1,10 UNION ALL

SELECT 7,3,40 UNION ALL

SELECT 7,1,10 UNION ALL

SELECT 7,2,5 UNION ALL

SELECT 8,3,10 UNION ALL

SELECT 9,3,15 UNION ALL

SELECT 9,2,10 UNION ALL

SELECT 10,1,20 UNION ALL

SELECT 11,1,10 UNION ALL

SELECT 12,3,40

--===== Solve the problem using a classic Cross Tab

SELECT Category,

SUM(CASE WHEN Month = 1 THEN Value ELSE 0 END) AS Jan,

SUM(CASE WHEN Month = 2 THEN Value ELSE 0 END) AS Feb,

SUM(CASE WHEN Month = 3 THEN Value ELSE 0 END) AS Mar,

SUM(CASE WHEN Month = 4 THEN Value ELSE 0 END) AS Apr,

SUM(CASE WHEN Month = 5 THEN Value ELSE 0 END) AS May,

SUM(CASE WHEN Month = 6 THEN Value ELSE 0 END) AS Jun,

SUM(CASE WHEN Month = 7 THEN Value ELSE 0 END) AS Jul,

SUM(CASE WHEN Month = 8 THEN Value ELSE 0 END) AS Aug,

SUM(CASE WHEN Month = 9 THEN Value ELSE 0 END) AS Sep,

SUM(CASE WHEN Month = 10 THEN Value ELSE 0 END) AS Oct,

SUM(CASE WHEN Month = 11 THEN Value ELSE 0 END) AS Nov,

SUM(CASE WHEN Month = 12 THEN Value ELSE 0 END) AS Dec,

SUM(Value) AS Total

FROM #Temp

GROUP BY Category

ORDER BY Category

DROP TABLE #Temp[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

I agree, Jeff. Pivot & Unpivot are extraordinarily difficult to use and read.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 8

Yeah, the PIVOT is very unintuitive.

Best Regards,

Chris Büttner