Query for one column to multiple columns

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

Query for one column to multiple columns

Author

Topic

rajsqlteam
Starting Member

14 Posts

Posted - 2012-12-03 : 10:55:38

Hello experts,

I am new to SQL programming. I am trying to query a table and get a result as shown below. Kindly provide your feedback.
Source table: (Line is a column stating number of lines person ID consists of)
ID AGE LINE Address
1 23 1 238 Wa AVE
2 21 1 234 Some st
3 45 1 776 oth st
1 23 2 Washington

Desired output: (ID should not repeat, instead add a new column address 2 in the same row)
ID AGE Adress 1 Address 2
1 23 238 Wa AVE Washington
2 21 234 Some st Null
3 45 776 other st Null

Pardon for not showing in tabular format. This is my first post!

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 10:58:13

[code]
SELECT ID,
AGE,
MAX(CASE WHEN LINE = 1 THEN Address END) AS Address1,
MAX(CASE WHEN LINE = 2 THEN Address END) AS Address2
FROM table
GROUP BY ID,
AGE
[/code]

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

rajsqlteam
Starting Member

14 Posts

Posted - 2012-12-03 : 11:08:40

Thanks much. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 11:11:50

welcome

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

rajsqlteam
Starting Member

14 Posts

Posted - 2012-12-03 : 11:34:38

Have another question related to this.

Lets say we dont have the Line column in the source table, How can we get both adresses in the result set?
Source Table:
ID AGE Address
1 23 238 Wa AVE
2 21 234 Some st
3 45 776 oth st
1 23 Washington

Output:
ID AGE Adress 1 Address 2
1 23 238 Wa AVE Washington
2 21 234 Some st Null
3 45 776 other st Null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 11:35:48

do you've any other unique valued column to determine the order?

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

rajsqlteam
Starting Member

14 Posts

Posted - 2012-12-03 : 11:46:41

Lets say order doesn't matter here..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 12:07:24

then this would be fine


SELECT ID,
 AGE,
 MAX(CASE WHEN LINE = 1 THEN Address END) AS Address1,
 MAX(CASE WHEN LINE = 2 THEN Address END) AS Address2
FROM (
SELECT *,ROW_NUMBER() OVER (PARTITION BY ID,AGE ORDER BY Address) AS LINE
FROM table
)t
GROUP BY ID,
AGE

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

rajsqlteam
Starting Member

14 Posts

Posted - 2012-12-04 : 09:48:30

Thank you very much!

But Let me be more clear on what exactly I am clicking.
I actually have two tables where ID is tied to a value as shown below,

ID value
1000 1
1002 1
1004 2
1000 3
1000 2
1002 2

So when I have a value of 1 for an ID it should show up 1, and for the same ID if I have a value of 2 it should show up as 1 else 0.
RESULT SET:

ID value1 value2 value3
1000 1 1 1
1002 1 1 0
1004 0 1 0

Please let me know.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-04 : 11:18:26

You will have to see Dynamic SQL. There are plenty of post on it. How you determine Value will go to Value1 or Value2 ........

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-04 : 11:47:54

WOuld depend on now many Values you expect. If you need code to work for N number of different values, then Sodeep is correct that you'd need Dynamic SQL. If it is a finite set, then you can write SQL easy enough to do that.

rajsqlteam
Starting Member

14 Posts

Posted - 2012-12-04 : 11:51:41

Thanks Sodeep and Lamprey!

@Lamprey

Yes the number of values I need is finite. As in the exmaple I just need 3, could you please let me know the sql?

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-04 : 12:21:29

[code]Select ID,
Case When CNT > 0 then 1 else 0 end as Value1,
Case When CNT > 1 and CNT <= 2 then 1 else 0 end as Value2,
Case When CNT > 2 and CNT <= 3 then 1 else 0 end as Value3
from
(
Select ID,Count(*) CNT
from Table
Group by ID
)P[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources