Simple Combine String Row Failing Hopelessly!

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 2005 Forums

Other SQL Server Topics (2005)

Simple Combine String Row Failing Hopelessly!

Author

Topic

lucyfr
Starting Member

9 Posts

Posted - 2011-08-02 : 11:52:52

Hi all,

Please forgive me if this has been posted before but I have been searching for about 3 solid days now and going round and round with no simple solution to this it seems... I can't pivot as I can't hardcode anything (or so I think), I can't merge in SQL Server 2005 (?) and most of the solutions online deal with aggregates and some sort of sum function. Nightmare when you just want some simple string movement!

Anywho, I have multiple rows (some 1, some up to 4 similar rows) with same ID but different tumour sites, e.g

ID sex site
12345 1 c456
12345 1 c791
12345 1 c867
44444 2 c475
44444 2 c697

I want to combine the rows to read:

ID sex site site2 site3 site4
12345 1 c456 c791 c867 NULL
44444 2 c475 c697 NULL NULL

I am currently using this rare code (kindly supplied on a forum) which stops working after 2 rows by just repeating the 2nd value or telling me b.ID is not bound, i.e. I don't know how to expand it using multiple left outer joins that return values by checking to see if there are more of the same ID numbers and moving all the required sites across:

use DB

SELECT
a.ID,a.sex,
[site1] = ISNULL((
SELECT TOP 1 site
FROM DB
WHERE ID = a.ID),''),
[site2] = ISNULL((
SELECT TOP 1 b.site
FROM DB b
WHERE b.ID = a.ID
AND site NOT IN(
SELECT TOP 1 site
FROM DB
WHERE ID = b.ID
)),''),
[site3] = ISNULL((
SELECT TOP 1 c.site
FROM DB c
WHERE c.ID = a.ID
AND site NOT IN(
SELECT TOP 1 site
FROM DB
WHERE ID = c.ID
)),'')
FROM DBt a
GROUP BY a.ID, a.sex;

Any help would be much appreciated!

Thanks!!!

Lucy
(Trying to fly the flag high for Occam's Razor!)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 12:06:44

[code]SELECT ID,sex,
MAX(CASE WHEN rn=1 THEN site ELSE NULL END) AS site1,
MAX(CASE WHEN rn=2 THEN site ELSE NULL END) AS site2,
MAX(CASE WHEN rn=3 THEN site ELSE NULL END) AS site3,
MAX(CASE WHEN rn=4 THEN site ELSE NULL END) AS site4
FROM
(
SELECT ID,sex,
ROW_NUMBER() OVER (PARTITION BY Id,sex ORDER BY site) AS rn
FROM table
)t
GROUP BY ID,sex
[/code]

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

lucyfr
Starting Member

9 Posts

Posted - 2011-08-03 : 04:48:40

Hi Visakh,

Thanks so much for the reply. I just tried it (presuming you meant the table name for "table" and "t") but it's telling me "invalid column name 'site'". If I add site to the list of columns in the select statement it runs but just repeats the first site value in random places in the results and still has 2 rows for each unique ID, which os course I need one row for each ID.

Thanks!

Lucy

lucyfr
Starting Member

9 Posts

Posted - 2011-08-03 : 04:56:14

Sorry Visakh!!!

I have just figured it out, used this as the select statement:

SELECT nhsnumber,sex,site,
ROW_NUMBER() OVER (PARTITION BY nhsnumber,sex ORDER BY nhsnumber) AS rn
FROM JANMinDist

and it worked!!! YEEEES!!!!! The end of 3 days of searching, I am cheering haha!!!!

Thank you so so much for taking the time out to help me with this one, i wills spread the knowledge :)

Lucy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 04:58:00

oops small correction


SELECT ID,sex,
MAX(CASE WHEN rn=1 THEN site ELSE NULL END) AS site1,
MAX(CASE WHEN rn=2 THEN site ELSE NULL END) AS site2,
MAX(CASE WHEN rn=3 THEN site ELSE NULL END) AS site3,
MAX(CASE WHEN rn=4 THEN site ELSE NULL END) AS site4
FROM
(
SELECT ID,sex,site,
ROW_NUMBER() OVER (PARTITION BY Id,sex ORDER BY site) AS rn
FROM table
)t
GROUP BY ID,sex

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

lucyfr
Starting Member

9 Posts

Posted - 2011-08-03 : 06:02:09

Aaah yes sorry pasted my actual variables!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 07:29:18

welcome

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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2011-08-03 : 15:49:55

>> GROUP BY ID,sex

sigmund freud would be proud.

elsasoft.org

lucyfr
Starting Member

9 Posts

Posted - 2011-08-17 : 08:54:12

Hi guys,

I'm sorry but I'm back

When running this query it works for some lines but then a lot are coming up as:

ID sex site site2 site3 site4
12345 2 c456 NULL NULL NULL
12345 2 NULL c697 NULL NULL

As in they seem to be inserting a NULL before the next value instead of moving it up and therefore causing 'duplicate' rows again...

Any thought?

THANK YOU

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-17 : 08:57:35

please show us your query

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

lucyfr
Starting Member

9 Posts

Posted - 2011-08-17 : 09:15:02

Hi Khtan,

same as suggested code from Visakh above but with a few more columns added into the select, e.g SELECT ID,sex.DOD.DOB, etc.

Thanks,

Lucy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-17 : 09:22:16

quote:
Originally posted by lucyfr

Hi Khtan,

same as suggested code from Visakh above but with a few more columns added into the select, e.g SELECT ID,sex.DOD.DOB, etc.

Thanks,

Lucy

that's the problem over there

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

lucyfr
Starting Member

9 Posts

Posted - 2011-08-17 : 09:23:25

So sorry, that should have read: SELECT ID,sex,DOD,DOB without the full stops! Huge aologies

lucyfr
Starting Member

9 Posts

Posted - 2011-08-17 : 09:49:39

This is an example of the code I am using:

use DB

SELECT id,sex,dod,dob,postcode,site,
MAX(CASE WHEN rn=1 THEN site ELSE NULL END) AS site1,
MAX(CASE WHEN rn=2 THEN site ELSE NULL END) AS site2,
MAX(CASE WHEN rn=3 THEN site ELSE NULL END) AS site3
FROM
(
SELECT id,sex,dod,dob,postcode,site,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
FROM Table s
) s
GROUP BY id,sex,dod,dob,postcode,site

I used the site code above twice successfully for different queries with only a few issues as stated above. Now it's not working at all and it just inserting the NULL value instead of moving the wanted value to the row above!

Very frustrating...

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-17 : 09:53:42

quote:

When running this query it works for some lines but then a lot are coming up as:

ID sex site site2 site3 site4
12345 2 c456 NULL NULL NULL
12345 2 NULL c697 NULL NULL

execute that query that you just posted, including the dob, postcode, site in the SELECT clause and look at the result. You will understand why

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-17 : 09:57:11

reason is simple
if you want only grouping based on ID and sex remove others from group by


SELECT id,sex,
MAX(CASE WHEN rn=1 THEN site ELSE NULL END) AS site1,
MAX(CASE WHEN rn=2 THEN site ELSE NULL END) AS site2,
MAX(CASE WHEN rn=3 THEN site ELSE NULL END) AS site3
FROM
(
SELECT id,sex,dod,dob,postcode,site,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
FROM Table s
) s
GROUP BY id,sex

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

lucyfr
Starting Member

9 Posts

Posted - 2011-08-23 : 09:41:53

Thanks Khtan! It's working now!

I thought I had tried every possible combination in the selects when it gave the 'invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause' error but obviously overlooked all of that!

Thanks also Visakh, I will remember that!

Great stuff, all the best guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources