Urgent Query Help Plz... - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Urgent Query Help Plz...

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2012-10-29 : 19:37:35

Hi guys,

Here is my target table structure...

ID,ZIPCODE,ZIPCODE2,ZIPCODE3,ZIPCODE4

Here is my source table

ID, ZIPCODE
1,2234
2,45678
1,2234
1,49800
1,5699

The end result should be
ID,ZIPCODE,ZIPCODE2,ZIPCODE3,ZIPCODE4
1,2234,49800,5699
2,45678,

Please help me out how i can accomplish this one in tsql.

Please let me know if you want more information.

Thank you for your help in advance...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 19:44:07

[code]
SELECT ID,
MAX(CASE WHEN Seq=1 THEN ZIPCODE END) AS ZIPCODE,
MAX(CASE WHEN Seq=2 THEN ZIPCODE END) AS ZIPCODE2,
MAX(CASE WHEN Seq=3 THEN ZIPCODE END) AS ZIPCODE3,
MAX(CASE WHEN Seq=4 THEN ZIPCODE END) AS ZIPCODE4
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq,*
FROM TABLE
)t
GROUP BY ID
[/code]

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

tooba
Posting Yak Master

224 Posts

Posted - 2012-10-29 : 20:47:50

Awesome, Thank you for your help... Its work fine. That's what i want!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 09:22:01

welcome

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

tooba
Posting Yak Master

224 Posts

Posted - 2012-10-30 : 13:39:25

Visakh, just want to know if in feature if requirement comes up like this...

ID, ZIPCODE
1,2234
2,45678
1,2234
1,49800
1,5699
3,98778
3,98778
3,98778

The end result should be
ID,ZIPCODE,ZIPCODE2,ZIPCODE3,ZIPCODE4
1,2234,49800,5699
2,45678,
3,98778,

I want only unique Zipcode.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 13:43:16

[code]
SELECT ID,
MAX(CASE WHEN Seq=1 THEN ZIPCODE END) AS ZIPCODE,
MAX(CASE WHEN Seq=2 THEN ZIPCODE END) AS ZIPCODE2,
MAX(CASE WHEN Seq=3 THEN ZIPCODE END) AS ZIPCODE3,
MAX(CASE WHEN Seq=4 THEN ZIPCODE END) AS ZIPCODE4
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq,
ROW_NUMBER() OVER (PARTITION BY ID,ZIPCODE ORDER BY ID) AS DupSeq
FROM TABLE
)t
WHERE DupSeq=1
GROUP BY ID
[/code]

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

tooba
Posting Yak Master

224 Posts

Posted - 2012-10-30 : 13:48:00

That was just a e.g, If i have more columns should i enter here

SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq,
ROW_NUMBER() OVER (PARTITION BY ID,ZIPCODE, ????????????? ORDER BY ID) AS DupSeq
FROM TABLE
)t
WHERE DupSeq=1
GROUP BY ID

tooba
Posting Yak Master

224 Posts

Posted - 2012-10-30 : 14:22:37

Please forgive me the requirement will change. Same logic however column different/more

ID, ZIPCODE,STATE
1,2234,NY
2,45678,CA
2,45678,CA
1,2234,NY
1,49800,KE
1,5699,WA
3,98778,VE
3,98778,VE
3,98778,VE

The end result should be
ID,ZIPCODE,ZIPCODE2,ZIPCODE3,ZIPCODE4,CITY1,CITY2,CITY3
1,2234,49800,5699, ,NY,KE,WA
2,45678,,,,CA
3,98778,,,,VE

Please guide me how i can accomplish this one....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 14:33:48

same way


SELECT ID,
MAX(CASE WHEN Seq=1 THEN ZIPCODE END) AS ZIPCODE,
MAX(CASE WHEN Seq=2 THEN ZIPCODE END) AS ZIPCODE2,
MAX(CASE WHEN Seq=3 THEN ZIPCODE END) AS ZIPCODE3,
MAX(CASE WHEN Seq=4 THEN ZIPCODE END) AS ZIPCODE4,
MAX(CASE WHEN Seq=1 THEN CITY END) AS CITY1,
MAX(CASE WHEN Seq=2 THEN CITY END) AS CITY2,
MAX(CASE WHEN Seq=3 THEN CITY END) AS CITY3
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq,
ROW_NUMBER() OVER (PARTITION BY ID,ZIPCODE,... (all columns that form duplicate group here) ORDER BY ID) AS DupSeq
FROM TABLE
)t
WHERE DupSeq=1
GROUP BY ID

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

tooba
Posting Yak Master

224 Posts

Posted - 2012-10-30 : 14:41:33

SELECT ID,
lname,
fname,
gender,
MAX(CASE WHEN Seq=1 THEN ZIPCODE1 END) AS ZIPCODE,
MAX(CASE WHEN Seq=2 THEN ZIPCODE1 END) AS ZIPCODE2,
MAX(CASE WHEN Seq=3 THEN ZIPCODE1 END) AS ZIPCODE3,
MAX(CASE WHEN Seq=4 THEN ZIPCODE1 END) AS ZIPCODE4,
MAX(CASE WHEN Seq=1 THEN CITY END) AS CITY1,
MAX(CASE WHEN Seq=2 THEN CITY END) AS CITY2,
MAX(CASE WHEN Seq=3 THEN CITY END) AS CITY3
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq,
ROW_NUMBER() OVER (PARTITION BY ID,lanme,fname,gender,ZIPCODE1,CITY) ORDER BY ID) AS DupSeq
FROM TABLE
)t
WHERE DupSeq=1
GROUP BY ID
LANME,
FNAME,
GENDER

Note:- Here is my sample query but i am not getting unique values. Please guide me where i am wrong.

Thank You.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 15:11:52

Hhow can i guide without knowing your data?
Show some sample data from your table and then explain what output you want out of it

b/w above posted query is not syntactically correct as you've extra braces inside partition by

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

tooba
Posting Yak Master

224 Posts

Posted - 2012-10-30 : 15:18:05

Yes that was typo mistake.

I understand, its hard to guide. I am following your direction.... I will create a sample data for you...

Thank You...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources