I am trying to convert rows to columns... not having much luck

Question

I am trying to convert rows to columns... not having much luck

williaal

SSC Rookie

Points: 37
More actions
May 16, 2008 at 2:49 pm

#186654

Hi Everyone,
I have searched through the forum, found some examples and couldnt get them to work. Below is what my current query returns. Please see the attachment if the table below doesnt make sense.
IssuedDateTimeSurveyName Question DisplaySequence Response
4/29/2008Survey1 FPS/Flow2252
4/29/2008Survey1 GPM038
4/29/2008Survey1 Level/IN363
4/29/2008Survey1 Daily Reading1184685
4/30/2008Survey1 FPS/Flow2115
4/30/2008Survey1 GPM036
4/30/2008Survey1 Level/IN324
4/30/2008Survey1 Daily Reading1195486
I would some how like it to convert like this:
IssuedDateTimeGPMDaily ReadingFPS/Flow Level/IN
4/29/200838184685 252 63
4/30/200836195486 115 24
I'm new to SQL and any advice would be greatly appreciated. Thanks--
williaal@msoe.edu

Viewing 11 posts - 1 through 11 (of 11 total)

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

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

Welcome aboard! Since you're new, lemme tell you that you'll get much better answers much more quickly if you do what suggested in the URL in my signature, in the future Take a look...

Also, lot's of folks would "pivot" this data using the PIVOT function of SQL Server 2005. Me? For some reason, I like "cross-tab reports" a whole lot better because they work in 2k and 2k5 just a well. In the presence of proper indexing, they just fly.

Here's a URL that explains cross-tabs better than I could...

http://msdn.microsoft.com/en-us/library/aa172756.aspx

The table you have is known to some as an "EAV" or "Entity, Attribute, Value" table. Others just call it a "Name/Value" table. Instead of having columns like a normal table, they have the "column names" stored in the table. Like anything else, they have advantages and disadvantages.

Anyway, here's the code... like many of us post, it includes the test data and table that we'd like to see people post just to make it easier for us to test. Lots of us won't post code unless we've actually been able to test it.

--===== Setup a demo table with some test data

-- This isn't part of the solution,

-- but it would be nice for you to provide in this manner

-- in the future. I give posts that have this, "priority".

DECLARE @YourTable TABLE

(IssuedDateTime DATETIME,

SurveyName VARCHAR(20),

Question VARCHAR(20),

DisplaySequence INT,

Response INT)

INSERT INTO @YourTable

(IssuedDateTime, SurveyName, Question, DisplaySequence,Response)

SELECT '4/29/2008','Survey1','FPS/Flow' ,'2','252' UNION ALL

SELECT '4/29/2008','Survey1','GPM' ,'0','38' UNION ALL

SELECT '4/29/2008','Survey1','Level/IN' ,'3','63' UNION ALL

SELECT '4/29/2008','Survey1','Daily Reading','1','184685' UNION ALL

SELECT '4/30/2008','Survey1','FPS/Flow' ,'2','115' UNION ALL

SELECT '4/30/2008','Survey1','GPM' ,'0','36' UNION ALL

SELECT '4/30/2008','Survey1','Level/IN' ,'3','24' UNION ALL

SELECT '4/30/2008','Survey1','Daily Reading','1','195486'

--===== Prepare for some nice clean output

-- Look these up in books online.

SET ANSI_WARNINGS OFF

SET NOCOUNT ON

--===== Here's the code that actually does what you want. It's a "cross-tab" report

SELECT CONVERT(CHAR(10),IssuedDateTime,101) AS IssuedDateTime,

MAX(CASE WHEN DisplaySequence = 0 THEN Response END) AS GPM,

MAX(CASE WHEN DisplaySequence = 1 THEN Response END) AS [Daily Reading],

MAX(CASE WHEN DisplaySequence = 2 THEN Response END) AS [FPS/Flow],

MAX(CASE WHEN DisplaySequence = 3 THEN Response END) AS [Level/IN]

FROM @YourTable

GROUP BY IssuedDateTime

And, here's what the output of that looks like...

[font="Courier New"]IssuedDateTime GPM Daily Reading FPS/Flow Level/IN

-------------- ----------- ------------- ----------- -----------

04/29/2008 38 184685 252 63

04/30/2008 36 195486 115 24[/font]

Like I said, welcome aboard and I hope this helps! 🙂

--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)

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

Jeff's answer is great.

I would like to ask: what is your presentation layer for this information?

I ask because although this type of pivot can be done in T-SQL, it is usually best handled at the presentation layer with a tool that can handle another "column" being added. You can do this with some clever dynamic SQL, but it's nice to use a tool made for it.

If you are using reporting services, I would suggest you try the Matrix control. If you are using Excel, I would suggest a PivotTable.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

In this case, since the same amount of data is being transmitted either way, it would probably be best to tax the client for the final format instead of having the server do it.

--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)

williaal SSC Rookie Points: 37 More actions · Answer 4

Thanks much for the help. Everything worked fantastic.

I am using SQL Server reporting. I am finding the DB design is less than stellar for use w/i reporting services, but this helped a great deal.

Thanks again.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Jeff Moden (5/19/2008)
In this case, since the same amount of data is being transmitted either way, it would probably be best to tax the client for the final format instead of having the server do it.

If only that were the case all of the time. The concern I have with that is that the aggregation tends to happen in reporting services, and NOT on the DB. The default query requested in a matrix report is a detail query, not an aggregate. The query sent from the report is a "straight" select, not the "sum" or whatever aggregate function you're pivoting. There can actually be a HUGE difference in records sent.

It's functionally akin to doing a client side pull of all of the rows, with client-side aggregation. The right report will "kill" your network traffic that way (under the assumption you have a dedicated Reporting Services server). Even if the two services are on the same physical machine - that's a lot of transferring between services.

So - while I wouldn't necessarily PIVOT the data on the server, I would definitively feed pre-aggregated data (say, using grouped aggregated data, that can then be pivoted/manipulated appropriately.)

At very least - be sure to build the SUM directly into the query being sent by SSRS.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Agreed! You do have to be a bit "pipe" sensitive...

--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)

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 7

But remember that the "pipe" being referred to is the one between the SQL Server and the RS Server. They could be on the same machine or connected to each other by a fiber switch.

It is almost always a good idea to aggregate at the SQL server whenever possible, but offloading some of the processing may be worth using additional bandwidth. So, like everything else, "it depends".

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

Heh... or like my old DBA used to say... "Got resources? Use 'em!" 😛

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Michael Earl (6/4/2008)
But remember that the "pipe" being referred to is the one between the SQL Server and the RS Server. They could be on the same machine or connected to each other by a fiber switch.
It is almost always a good idea to aggregate at the SQL server whenever possible, but offloading some of the processing may be worth using additional bandwidth. So, like everything else, "it depends".

No doubt....If you're starved for a given resource will force you to get 'creative'.

Still - the perf differences can be astounding: I fixed one this morning and it went from 42s to 147ms....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 10

THAT's why I sometimes get a bit angry with the folks that insist that everything must be done on the client side... server spends more time piping stuff over the side that it would ever spend doing the actual work.

Heh... same thing with most CLR's...

--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)