Grouping Data and Selecting fields with Data

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)

Grouping Data and Selecting fields with Data

Author

Topic

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-05-09 : 10:19:48

Hello,

I am trying to do a Group based off of ID that will grab the data from other fields that return data. For example:


ID     FNAME   LNAME    DOB        Scars        SSN
---    -----   -----    -----      ------     -------
1      Bob     Spikes   03031991   left arm   
1      Bob     Spikes   03031991              309309390
1      Bob     Spikes   03031991   arm
2      Sam     Amos     08151953   shoulder   398493893
3      Sarah   Cunning  08161932   back       
4      Joey    Smith    04201940              
4      Joey    Smith    04201940   right arm  909803930

When these records get grouped I would like it to look like this based off - which has the longest length and which has data...


ID     FNAME   LNAME    DOB        Scars        SSN
---    -----   -----    -----      ------     -------
1      Bob     Spikes   03031991   left arm   309309390
2      Sam     Amos     08151953   shoulder   398493893
3      Sarah   Cunning  08161932   back       
4      Joey    Smith    04201940   right arm  909803930

Right now I am on the track of thinking it would be best to use a group by and a cte... I just haven't been able to figure the part of yet where it is going to select the pieces from each row.

Here is some sample data that is in the above example:


CREATE TABLE #TestTable (ID int, fname varchar(15), lname varchar(20), dob varchar(8), scars varchar(30), ssn varchar(9)) 

INSERT INTO #TestTable values (1,'Bob','Spikes','03031991','left arm','')
INSERT INTO #TestTable values (1,'Bob','Spikes','03031991','','309309390')
INSERT INTO #TestTable values (1,'Bob','Spikes','03031991','arm','')
INSERT INTO #TestTable values (2,'Sam','Amos','08151953','shoulder','398493893')
INSERT INTO #TestTable values (3,'Sarah','Cunning','08161932','back','')
INSERT INTO #TestTable values (4,'Joey','Smith','04201940','','')
INSERT INTO #TestTable values (4,'Joey','Smith','04201940','right arm','909803930')

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-05-09 : 12:00:01

*** UPDATE ***

Alright, I think I have figured out a way to do it but it isn't the cleanest by any means... and also will make a lot of extra columns that won't work fully the way that it should... it is just a temp solution until one of the experts on here can show me up :P

I am not wanting to concatenate the rows together and am assuming that the field that has the longest scar is going to be the best choice.


go
--This alters the table that I posted... this could be just added to the original create 
ALTER TABLE #TestTable
ADD RankID int

go
;with cte as 
(
	select
	ROW_NUMBER() over (PARTITION BY ID ORDER BY ID, LEN(SCARS)) as grp, rankID
	
	from #testTable
) 
update cte set rankid = grp;

SELECT * FROM #TestTable
order by ID, rankid desc

Also, I added a little more sample data to make sure that it is working as it should be...


go
INSERT INTO #TestTable values (1,'Bob','Spikes','03031991','left arm','')
INSERT INTO #TestTable values (1,'Bob','Spikes','03031991','','309309390')
INSERT INTO #TestTable values (1,'Bob','Spikes','03031991','arm','')
INSERT INTO #TestTable values (1,'Bob','Spikes','03031991','left arm, shoulder, back','')
INSERT INTO #TestTable values (1,'Bob','Spikes','03031991','back','')
INSERT INTO #TestTable values (2,'Sam','Amos','08151953','shoulder','398493893')
INSERT INTO #TestTable values (3,'Sarah','Cunning','08161932','back','')
INSERT INTO #TestTable values (4,'Joey','Smith','04201940','','')
INSERT INTO #TestTable values (4,'Joey','Smith','04201940','right arm','909803930')

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-05-09 : 21:57:52

;with cte as
(
select
ROW_NUMBER() over (PARTITION BY ID ORDER BY LEN(SCARS) desc) as grp, *

from #testTable
)
select
ID , fname , lname , dob , scars , ssn
from cte
where
grp=1
order by ID

and don't use strings as dates :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-10 : 00:51:07

sounds like this to me seeing the sample data and output


SELECT ID,fname , lname , dob,
MAX(CASE WHEN ScarSeq = 1 THEN Scars END) AS Scars,
MAX(CASE WHEN SSNSeq = 1 THEN SSN END) AS SSN 
FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(Scars) DESC) AS ScarSeq,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(SSN) DESC) AS SSNSeq
FROM Table
)t
GROUP BY ID,fname , lname , dob

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-05-10 : 10:49:05

@LoztInSpace

Good solution but I think Visakh16 is right on the money... and trust me I hate the string for date, too... I deal with converting client data to our system so you'd be appalled at some of the stuff I have to deal with :)

@Visakh16

Thanks a billion! I was getting close but that script works flawlessly (like every other of your scripts that I have seen do). People like you are why I will always keep coming to this forum and why I recommend all the people I help in college with regards to SQL to this forum... Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-13 : 01:27:10

quote:
Originally posted by cstokes91

@LoztInSpace

Good solution but I think Visakh16 is right on the money... and trust me I hate the string for date, too... I deal with converting client data to our system so you'd be appalled at some of the stuff I have to deal with :)

@Visakh16

Thanks a billion! I was getting close but that script works flawlessly (like every other of your scripts that I have seen do). People like you are why I will always keep coming to this forum and why I recommend all the people I help in college with regards to SQL to this forum... Thanks again!

You're welcome

Glad that I could be of help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources