T-SQL Help

Question

Post reply

T-SQL Help

MattW2010

SSCommitted

Points: 1532
More actions
March 15, 2011 at 9:02 am

#236756

Hi,
The following is a sample of the data I am working with:
CREATE TABLE #Temp1
(ID INT, Code INT)
INSERT INTO #Temp1
VALUES
('1','1510'),
('1','1515'),
('1','1520'),
('1','1525'),
('2','1510'),
('2','1515'),
('2','1520'),
('2','1525'),
('3','1510'),
('3','1580'),
('4','1510'),
('4','1590')
SELECT * FROM #Temp1 ORDER BY ID
You'll notice there are 4 groups identified by the ID column. Each group has a set up codes assigned to them. What I'm having trouble figuring out is how to group a group. In other words, I'm trying to add a third column that groups each code set per ID. So, for example, ID 1 has 4 codes and ID 2 has 4 codes. The codes in both ID 1 and 2 are identical. Thus, in the third row we would identify the first 8 rows with a 1.
However, the codes assigned to the 3rd ID and the 4th ID do not equal any other set of codes so since they are unique, they would be given their own idenitificaiton in row 3, e.g. 2 and 3.
I have tried multiple versions of RANK, ROW_NUMBER(), and DENSE_RANK for the value in the third column. I have successfully written a CTE query that solves the issue, however, the business side wants to be able to sort the data in Excel by code. Therefore, creating a comma separated list of the codes in one row will not suffice.
Can anyone please offer any help?
Thanks,
Matt

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95405 More actions · Answer 1

Matt,

Based on the sample data that you provided (BTW - THANK YOU!!!), can you show us what the expected output is?

(Question - is the data really INT? You're putting character data into it (though it will be implicitly converted to int).)

Edit: would you also post your CTE that solves the problem?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

MattW2010 SSCommitted Points: 1532 More actions · Answer 2

Hi,

Thanks for the reply! What I want to see is this (sorry Excel doesn't copy over very well):

IDCodeGroupingGroupingOther

115101A

115151A

115201A

115251A

215101A

215151A

215201A

215251A

315102B

315802B

415103C

415903C

The "Grouping" or "GroupOther" columns identifies all the different code sets and if a code set is identical to another, it uses the same identifier (1 or A in this case). Man, that sounds confusing.

You can see how much easier it is to do via a CTE. Basically I just completed a CTE that grouped each grouping and counted them. Unfortunately, I can't do that b/c the business wants to sort each individual code in Excel, i.e. do more analysis based on the groups I give them.

Yea, I am trying to figure this out as quickly as possible so I didn't even think of using the correct data types, but you are certainly correct. My bad.

MattW2010 SSCommitted Points: 1532 More actions · Answer 3

CTE Query:

CREATE TABLE #Temp1

(ID INT, Code VARCHAR(4))

INSERT INTO #Temp1

VALUES

('1','1510'),

('1','1515'),

('1','1520'),

('1','1525'),

('2','1510'),

('2','1515'),

('2','1520'),

('2','1525'),

('3','1510'),

('3','1580'),

('4','1510'),

('4','1590')

;WITH CTE AS

(

SELECT DISTINCT ID

FROM #Temp1

)

SELECT

ID,

CommaList = STUFF((

SELECT ',' + Code

FROM #Temp1

WHERE ID = CTE.ID

ORDER BY ID

FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')

FROM CTE

ORDER BY ID;

WayneS SSC Guru Points: 95405 More actions · Answer 4

Well, just expanding upon your cte, we can get the other columns that you're looking for:

(Note that I shifted over to using a table variable instead of the temp table for testing purposes only.)

DECLARE @test-2 TABLE (ID INT, code INT);

INSERT INTO @test-2

VALUES

('1','1510'),

('1','1515'),

('1','1520'),

('1','1525'),

('2','1510'),

('2','1515'),

('2','1520'),

('2','1525'),

('3','1510'),

('3','1580'),

('4','1510'),

('4','1590');

;

WITH cte1 AS

(

-- get the list of codes, sorted by code, for the ID for this row

SELECT t.*, Codes = STUFF((SELECT ',' + CONVERT(CHAR(4),code) FROM @test-2 t1 WHERE t1.id = t.id ORDER BY code FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')

FROM @test-2 t

), cte2 AS

(

-- perform a dense_rank on the order of the codes

-- dense_rank to not skip numbers

SELECT ID, code,

[Grouping] = DENSE_RANK() OVER (ORDER BY codes)

FROM cte1

)

-- add 64 to the grouping and get that character value

SELECT ID, code, [Grouping], GroupingOther = CHAR([Grouping] + 64)

FROM cte2;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

MattW2010 SSCommitted Points: 1532 More actions · Answer 5

Pretty sure you're my hero now Wayne..Thanks so much for your help. That definitely worked and is exactly what I'm looking for!

WayneS SSC Guru Points: 95405 More actions · Answer 6

Thanks for the feedback Matt. To be fair, you actually did get most of the way there yourself. Do you have any questions about this?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

MattW2010 SSCommitted Points: 1532 More actions · Answer 7

I don't have any questions. Thanks for asking. I actually already got feedback from the biz and that's exactly what they were looking for!

WayneS SSC Guru Points: 95405 More actions · Answer 8

MattW2010 (3/15/2011)
I actually already got feedback from the biz and that's exactly what they were looking for!

😎

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

MattW2010 (3/15/2011)
I don't have any questions. Thanks for asking. I actually already got feedback from the biz and that's exactly what they were looking for!

You should ask the "biz" what they want to do when the new column goes just one more than the letter "Z". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

The most recent code offered by Wayne S works exactly the way you want it to. Try it.

However, the problem remains... what if you have more than 26 groups (ie: more than A-Z). What type of group indication does the "biz" want then?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

MattW2010 SSCommitted Points: 1532 More actions · Answer 11

Hi Jeff,

I don't think it matters. That column was more for illustration to help make clear the issue. The numbered column provided was sufficient for the business needs.

Thanks,

Matt

WayneS SSC Guru Points: 95405 More actions · Answer 12

Jeff Moden (3/16/2011)
The most recent code offered by Wayne S works exactly the way you want it to. Try it.

Jeff, I think that you're replying to the message just above yours... it's one of "those" spams where the spammer grabs some text from one of the posts in the thread (in this case, the original post), then adds links to stores.

And then again, I could be wrong about what you're replying to... 😀

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

No, you're correct. That's the one I was replying to. Damned spammers. I wish I could reverse the tables on them. I'll report it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

MattW2010 (3/16/2011)
Hi Jeff,
I don't think it matters. That column was more for illustration to help make clear the issue. The numbered column provided was sufficient for the business needs.
Thanks,
Matt

Thanks for the feedback, Matt. Just making sure it didn't blow up on you. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions