| Author |
 Topic  |
|
|
rajsqlteam
Starting Member
14 Posts |
 Posted - 12/03/2012 : 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
India
47171 Posts |
Posted - 12/03/2012 : 10:58:13
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
rajsqlteam
Starting Member
14 Posts |
Posted - 12/03/2012 : 11:08:40
|
Thanks much. :)
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47171 Posts |
Posted - 12/03/2012 : 11:11:50
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rajsqlteam
Starting Member
14 Posts |
Posted - 12/03/2012 : 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
India
47171 Posts |
Posted - 12/03/2012 : 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 - 12/03/2012 : 11:46:41
|
| Lets say order doesn't matter here.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47171 Posts |
Posted - 12/03/2012 : 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 - 12/04/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/04/2012 : 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
Flowing Fount of Yak Knowledge
3832 Posts |
Posted - 12/04/2012 : 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 - 12/04/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/04/2012 : 12:21:29
|
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 |
|
|
| |
Topic |
|
Query for one column to multiple columns
