New Columns From Row Info - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

New Columns From Row Info

Author

Topic

stharish
Starting Member

43 Posts

Posted - 2012-04-16 : 20:28:33

T-SQL reporting using Report Builder...

I'm wondering what is the best method (syntax) for creating new columns from rows with duplicating information (in this example, Dept).

Example:

How can I get this...

Dept Name
100 Steve
100 Sarah
100 Joe
200 Sam
200 Jessica
200 Kristin
300 Paul
300 Mike
300 Bill
400 Troy
400 Kevin

Into a table like this...

Dept Name1 Name2 Name3
100 Steve Sarah Joe
200 Sam Jessica Kristin
300 Paul Mike Bill
400 Troy Kevin -

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-17 : 04:18:36

Refer this post
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

stharish
Starting Member

43 Posts

Posted - 2012-04-17 : 12:01:26

I'm still a beginner so unfortunately the link wasn't too helpful. Any additional help (hand-holding) would be much appreciated. :)

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-17 : 12:56:12

if its always three names you want pivot you can use below


SELECT Dept,
MAX(CASE WHEN Rn=1 THEN Name END) AS Name1,
MAX(CASE WHEN Rn=2 THEN Name END) AS Name2,
MAX(CASE WHEN Rn=3 THEN Name END) AS Name3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Dept) AS Rn,* FROM Table
)t
GROUP BY Dept

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stharish
Starting Member

43 Posts

Posted - 2012-04-27 : 23:54:03

I have inserted actual tables and fields below.

tm_flights is a table of flights
tm_flightpax is a table of flight passengers joined on FlightID
UserID is a distinct value within the tm_flightpax table for each passenger

Running the query I get "column 'flightID' was specified multiple times for 't' "

Did I do something wrong??

SELECT

[FlightID],
MAX(CASE WHEN Rn=1 THEN UserID END) AS Name1,
MAX(CASE WHEN Rn=2 THEN UserID END) AS Name2,
MAX(CASE WHEN Rn=3 THEN UserID END) AS Name3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY F.FlightID ORDER BY F.FlightID) AS Rn,* FROM tm_Flights as F

INNER JOIN tm_FlightPax as FP
ON FP.FlightID=FP.FlightID
)t

GROUP BY [FlightID]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-28 : 00:03:28

run the query below and you will see that


SELECT ROW_NUMBER() OVER (PARTITION BY F.FlightID ORDER BY F.FlightID) AS Rn, *
FROM tm_Flights as F
INNER JOIN tm_FlightPax as FP
ON FP.FlightID=FP.FlightID

KH
[spoiler]Time is always against us[/spoiler]

stharish
Starting Member

43 Posts

Posted - 2012-04-28 : 00:10:32

That query ran just fine...tons of rows though. Now what?

Again, I'm trying to get the UserID's into the Name1, Name2, Name3, etc columns for each distinct flightID

To further clarify:

I have a table of flights, a table of flight passengers, and a table of users. I can join the flights table with the flight passengers table by flightID. I can join the flight passengers table with the users table by UserID.

The resulting table needs to display each flight by FlightID with additional columns for each passenger for each flight

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-28 : 00:13:21

if you run that query you will notice that the FlightID appear twice.

Don't use *, specify only the required column name in that query

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-28 : 00:26:03

ok. to be more specific, changed that query to


SELECT
       [FlightID],
       MAX(CASE WHEN Rn=1 THEN UserID END) AS Name1,
       MAX(CASE WHEN Rn=2 THEN UserID END) AS Name2,
       MAX(CASE WHEN Rn=3 THEN UserID END) AS Name3
FROM
(
       SELECT ROW_NUMBER() OVER (PARTITION BY F.FlightID ORDER BY F.FlightID) AS Rn,
              F.FlightID, UserID
       FROM   tm_Flights as F
              INNER JOIN tm_FlightPax as FP ON FP.FlightID = FP.FlightID
)t
GROUP BY [FlightID]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources