Transpose Verticle to Horizontal

Question

Post reply

Transpose Verticle to Horizontal

pkaraffa

SSC Enthusiast

Points: 115
More actions
December 23, 2010 at 11:07 am

#232707

Good Afternoon,
I have a table that is set up vertically. Is it possible to get this horizontal. Below is what I am looking for. Thanks in advance!
SCRIDDAYPresent
5328/30/2010TRUE
5338/30/2010TRUE
5348/30/2010TRUE
5358/30/2010FALSE
5368/30/2010TRUE
5328/31/2010TRUE
5338/31/2010TRUE
5348/31/2010TRUE
5358/31/2010TRUE
5368/31/2010TRUE
5329/1/2010FALSE
5339/1/2010TRUE
5349/1/2010TRUE
5359/1/2010TRUE
5369/1/2010FALSE
8/30/20108/31/20109/1/2010
532110
533111
534111
535011
536110

Viewing 7 posts - 1 through 6 (of 6 total)

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

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 1

You need a pivot query. You can use the PIVOT syntax or do something similar to the query below.

I've assumed this table structure, so you will have to adapt the query if yours is different.

CREATE TABLE #T (

SCRID int,

[DAY] date,

Present tinyint

)

INSERT INTO #T(SCRID, [DAY], Present)

SELECT 532, '20100830', 1 UNION ALL

SELECT 533, '20100830', 1 UNION ALL

SELECT 534, '20100830', 1 UNION ALL

SELECT 535, '20100830', 0 UNION ALL

SELECT 536, '20100830', 1 UNION ALL

SELECT 532, '20100831', 1 UNION ALL

SELECT 533, '20100831', 1 UNION ALL

SELECT 534, '20100831', 1 UNION ALL

SELECT 535, '20100831', 1 UNION ALL

SELECT 536, '20100831', 1 UNION ALL

SELECT 532, '20100901', 0 UNION ALL

SELECT 533, '20100901', 1 UNION ALL

SELECT 534, '20100901', 1 UNION ALL

SELECT 535, '20100901', 1 UNION ALL

SELECT 536, '20100901', 0

SELECT SCRID,

MAX(D1) AS [2010-08-30],

MAX(D2) AS [2010-08-31],

MAX(D3) AS [2010-09-01]

FROM (

SELECT SCRID,

CASE WHEN [DAY] = '20100830' THEN Present ELSE NULL END AS D1,

CASE WHEN [DAY] = '20100831' THEN Present ELSE NULL END AS D2,

CASE WHEN [DAY] = '20100901' THEN Present ELSE NULL END AS D3

FROM #T

) PVT

GROUP BY SCRID

ORDER BY SCRID

If your date columns need to be dynamic, then you will need to use dynamic SQL.

LutzM SSC Guru Points: 107049 More actions · Answer 2

As an alternative you could use the CrossTab syntax as described in the related article in my signature.

If you'd need to add some flexibility, use DynamicCrossTab (a related article is also part of my signature...)

I, personally, find the CrossTab syntax more intuitive. And it performs better than PIVOT, too.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

pkaraffa SSC Enthusiast Points: 115 More actions · Answer 3

Thanks andrewd.smith that worked very well for a small amount of data (actually great) but I am dealing with over 500,000 rows and about a thousand different dates. LutzM I am a complete newby I am reading up on your Cross tabs. Is there any other way? A proceedure maybe? Thanks again for looking into this post.

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 4

pkaraffa (12/26/2010)
Thanks andrewd.smith that worked very well for a small amount of data (actually great) but I am dealing with over 500,000 rows and about a thousand different dates. LutzM I am a complete newby I am reading up on your Cross tabs. Is there any other way? A proceedure maybe? Thanks again for looking into this post.

Yes... the crosstab methodology can be incorporated in a stored procedure. It's really not any different than any other method that you'd incorporate into a stored procedure.

And, by the way, Andrew did use "crosstab" technology and not the slower Pivot technology (or at least in his final edit he did... I don't know what was there before the edit he made on the 23rd).

As a bit of a side bar, I have to ask... if you're a "complete newby" at such a thing, why do they have you doing this non-trivial task?

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

pkaraffa SSC Enthusiast Points: 115 More actions · Answer 5

Jeff Moden (12/26/2010)
pkaraffa (12/26/2010)
Thanks andrewd.smith that worked very well for a small amount of data (actually great) but I am dealing with over 500,000 rows and about a thousand different dates. LutzM I am a complete newby I am reading up on your Cross tabs. Is there any other way? A proceedure maybe? Thanks again for looking into this post.
Yes... the crosstab methodology can be incorporated in a stored procedure. It's really not any different than any other method that you'd incorporate into a stored procedure.
And, by the way, Andrew did use "crosstab" technology and not the slower Pivot technology (or at least in his final edit he did... I don't know what was there before the edit he made on the 23rd).
As a bit of a side bar, I have to ask... if you're a "complete newby" at such a thing, why do they have you doing this non-trivial task?

Thanks Jeff for the response. Not a complete newbie to everything, I usually use Excel, Access, or Crystal Reports to pull stuff out or manipulate it just a newbie to advanced SQL queries I can get by on some easy things but have not spent alot of time with some of the more difficult things. I can write code in Excel and Access but have not work alot in SQL. Just trying to learn as much as I can and thanks to Forums like this it provides me with excellent information and suggestions!

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

pkaraffa (12/27/2010)
Jeff Moden (12/26/2010)
pkaraffa (12/26/2010)
Thanks andrewd.smith that worked very well for a small amount of data (actually great) but I am dealing with over 500,000 rows and about a thousand different dates. LutzM I am a complete newby I am reading up on your Cross tabs. Is there any other way? A proceedure maybe? Thanks again for looking into this post.
Yes... the crosstab methodology can be incorporated in a stored procedure. It's really not any different than any other method that you'd incorporate into a stored procedure.
And, by the way, Andrew did use "crosstab" technology and not the slower Pivot technology (or at least in his final edit he did... I don't know what was there before the edit he made on the 23rd).
As a bit of a side bar, I have to ask... if you're a "complete newby" at such a thing, why do they have you doing this non-trivial task?
Thanks Jeff for the response. Not a complete newbie to everything, I usually use Excel, Access, or Crystal Reports to pull stuff out or manipulate it just a newbie to advanced SQL queries I can get by on some easy things but have not spent alot of time with some of the more difficult things. I can write code in Excel and Access but have not work alot in SQL. Just trying to learn as much as I can and thanks to Forums like this it provides me with excellent information and suggestions!

I wasn't referring to anything but what you were... T-SQL. 😉 Anyway, are you all set or do you need some more help on this particular problem?

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