SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Grouping Data and Selecting fields with Data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cstokes91
Yak Posting Veteran

USA
59 Posts

Posted - 05/09/2013 :  10:19:48  Show Profile  Reply with Quote
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

USA
59 Posts

Posted - 05/09/2013 :  12:00:01  Show Profile  Reply with Quote
*** 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')

Edited by - cstokes91 on 05/09/2013 12:08:46
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 05/09/2013 :  21:57:52  Show Profile  Reply with Quote

;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 :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/10/2013 :  00:51:07  Show Profile  Reply with Quote
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
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
59 Posts

Posted - 05/10/2013 :  10:49:05  Show Profile  Reply with Quote
@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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/13/2013 :  01:27:10  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000