row as column

Question

row as column

sudarsanan.kaliyamurthy

Old Hand

Points: 322
More actions
November 13, 2009 at 3:03 am

#212286

I have a table
empid date shift1 shift2
1 12/11/2009 first second
1 13/11/2009 first second
2 12/11/2009 first second
now i want the table to show as
empid date(12/11/2009) date(13/11/2009)
1 shift1 and Shift2 shift1 And shift2
is it possible to get this.
could someone help me?
thanks in advance.

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please see the links in my signature regarding Croos Tab and Dynamic Cross tab.

If you need further assistance please provide sample data in a ready to use format as described in the first link below.

Also, please consider to provide sample data that cover your scenario (using your sample it looks like shift1 and shift2 will always have the same value for each row...).

Side note: You might want to consider normalizing this table. What would happen if you need to add a third shift?

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]

sudarsanan.kaliyamurthy Old Hand Points: 322 More actions · Answer 2

sudarsanan.kaliyamurthy

Old Hand

Points: 322

November 13, 2009 at 4:37 am

#1078819

I have attached the sample data and the required table.

LutzM SSC Guru Points: 107049 More actions · Answer 3

sudarsanan.kaliyamurthy (11/13/2009)
I have attached the sample data and the required table.

Well, doesn't really look like it...

A table definition would be something along CREATE TABLE... and sample data would be INSERT INTO ... SELECT UNION ALL.

Please help us help you by providing ready to use data so we can work on the solution and not spending too much time to set up the sample data.

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]

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

sudarsanan.kaliyamurthy (11/13/2009)
I have attached the sample data and the required table.

You'll be amazed at how quickly you'll get an answer if you study the link that Lutz directed you to and post the CREATE TABLE statement along with readily consumable data using the methods in that article.

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

sudarsanan.kaliyamurthy Old Hand Points: 322 More actions · Answer 5

sudarsanan.kaliyamurthy

Old Hand

Points: 322

November 13, 2009 at 11:19 am

#1079137

Hi all,

I have attached a sample data.

Please help!!!

LutzM SSC Guru Points: 107049 More actions · Answer 6

Well, we're getting closer...

Following please find a proposal:

SET dateformat dmy

SELECT Emp_Id,

MAX(CASE

WHEN [Shift_Date] = '12/11/2009'

THEN ISNULL(Shift_Regular,'')

+ CASE

WHEN Shift_OnCall IS NOT NULL

THEN ' and ' +ISNULL(Shift_Regular,'')

ELSE ''

END

ELSE ''

END

) AS '11/12/2009',

MAX(CASE

WHEN [Shift_Date] = '12/11/2009'

THEN ISNULL(Shift_Regular,'')

+ CASE

WHEN Shift_OnCall IS NOT NULL

THEN ' and ' +ISNULL(Shift_Regular,'')

ELSE ''

END

ELSE ''

END

) AS '12/11/2009',

MAX(CASE

WHEN [Shift_Date] = '13/11/2009'

THEN ISNULL(Shift_Regular,'')

+ CASE

WHEN Shift_OnCall IS NOT NULL

THEN ' and ' +ISNULL(Shift_Regular,'')

ELSE ''

END

ELSE ''

END

) AS '13/11/2009',

MAX(CASE

WHEN [Shift_Date] = '14/11/2009'

THEN ISNULL(Shift_Regular,'')

+ CASE

WHEN Shift_OnCall IS NOT NULL

THEN ' and ' +ISNULL(Shift_Regular,'')

ELSE ''

END

ELSE ''

END

) AS '14/11/2009'

FROM [Emp_Shift_Details]

GROUP BY Emp_Id

/*

Emp_Id11/12/200912/11/200913/11/200914/11/2009

1first and firstfirst and firstfirst and firstfirst

2first and firstfirst and firstfirst and first

*/

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]

sudarsanan.kaliyamurthy Old Hand Points: 322 More actions · Answer 7

Hi Lutz,

Thanks a lot for helping me out.

Sorry for late reply.

I have attached a little bit modified query and could you please help me?

Once again thanks a lot for your assistance