compressing / collapsing numbers

Question

Post reply

compressing / collapsing numbers

juergen.kessler

SSC Rookie

Points: 30
More actions
October 30, 2008 at 7:04 am

#70935

Hello,
I'm new to the forum and need some help.
I want to compress (collapse) number rows, see the examples below:
This is the table I have:
idnr color
1 red
2 red
5 blue
6 blue
7 blue
12 green
15 yellow
16 yellow
17 yellow
This is what I need to get:
from_idnr thru_idnr color
1 2 red
5 7 blue
12 12 green
15 17 yellow
The real tables are a little bit more complicated, however, I don't know how to group the ranges.
Any help would be appreciated.
Thank you very much.
Juergen

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

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

Greg Snidow SSCoach Points: 16108 More actions · Answer 1

juergen, this will give you what you want based on your sample data.

SELECT

from_idnr = MIN(idnr),

to_idnr = MAX(idnr),

color

FROM #color

GROUP BY color

ORDER BY from_idnr

However, it will no longer work if you, say, for example, add idnr = 18 and color = 'red', which will give you output like this;

118red

57blue

1212green

1517yellow

So, there are ways around this, but not without making some assumptions about your table. Can you post the table structure please?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Garadin One Orange Chip Points: 29613 More actions · Answer 2

The link in my signature will give you an example of the proper way to post the information Greg is asking for. It's a good article to read for any new poster on the forum, and will help you get better, faster answers.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

juergen.kessler SSC Rookie Points: 30 More actions · Answer 3

Greg,

thank you for the reply.

Exactly what you mentioned will happen.

You wrote:

"However, it will no longer work if you, say, for example, add idnr = 18 and color = 'red', which will give you output like this; ....... "

There can be appear many equal columns in different rows not following each other.

1 red

2 blue

3 blue

4 red

5 red

6 red

should result in

1 1 red

2 3 blue

4 6 red

@Seth: Sorry Seth, will follow you next time.

Thank you in advance

Juergen

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

juergen.kessler (10/31/2008)
@Seth: Sorry Seth, will follow you next time.

If you do it this time, you'll actually get a tested answer... 😉

--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)

juergen.kessler SSC Rookie Points: 30 More actions · Answer 5

@jeff,

Thank you, I followed your instructions hopefully correct.

Hello All,

below you find a table with assembly parts example data.

You will find 2 important columns for the select in question: "model" and "whereabout".

The select I search for should produce the following output (simplified to 3 rows):

model_from model_to whereabout

1 3 customer1

7 8 customer2

11 11 customer3

15 17 customer1

Any help would be appreciated.

Thank you in advance.

Juergen

--------------------------------------------- snip ---------------------------------------------

IF OBJECT_ID('assembly_master_test','U') IS NOT NULL

DROP TABLE assembly_master_test

GO

CREATE TABLE [dbo].[assembly_master_test](

[identifier] [int] IDENTITY(1,1) NOT NULL,

[group_identifier] [int] NULL,

[id_model] [int] NOT NULL,

[model] [int] NOT NULL,

[node] [int] NOT NULL,

[checked] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,

[date] [datetime] NOT NULL,

[whereabout] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,

[text] [nvarchar](250) COLLATE Latin1_General_CI_AS NULL,

[version] [int] NOT NULL DEFAULT ((0)),

[contains_slave] [smallint])

GO

set dateformat DMY;

set identity_insert assembly_master_test on;

INSERT INTO assembly_master_test

([identifier]

,[group_identifier]

,[id_model]

,[model]

,[node]

,[checked]

,[date]

,[whereabout]

,[text]

,[version]

,[contains_slave])

select 0,1000,1,1,1,'myself',getdate(),'customer1','example',1,0 UNION ALL

select 0,1001,20,2,1,'myself',getdate(),'customer1','example',1,0 UNION ALL

select 0,1002,31,3,1,'myself',getdate(),'customer1','example',1,0 UNION ALL

select 0,1003,18,7,1,'myself',getdate(),'customer2','example',1,0 UNION ALL

select 0,1004,3,8,1,'myself',getdate(),'customer2','example',1,0 UNION ALL

select 0,1005,28,11,1,'myself',getdate(),'customer3','example',1,0 UNION ALL

select 0,1006,40,15,1,'myself',getdate(),'customer1','example',1,0 UNION ALL

select 0,1007,4,16,1,'myself',getdate(),'customer1','example',1,0 UNION ALL

select 0,1008,11,17,1,'myself',getdate(),'customer1','example',1,0

set identity_insert assembly_master_test off;

SELECT 'SELECT '+

QUOTENAME(identifier,'''')+','+

QUOTENAME(group_identifier,'''')+','+

QUOTENAME(id_model,'''')+','+

QUOTENAME(model,'''')+','+

QUOTENAME(node,'''')+','+

QUOTENAME(checked,'''')+','+

QUOTENAME(date,'''')+','+

QUOTENAME(whereabout,'''')+','+

QUOTENAME(text,'''')+','+

QUOTENAME(version,'''')+','+

QUOTENAME(contains_slave,'''')+','+

' UNION ALL'

FROM assembly_master_test;

select * from assembly_master_test

--------------------------------------------- snap ---------------------------------------------

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Trying... to... resist...

Gah.

Jeff Moden explains this technique in his article located here:

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

If you don't read that article, make note of several things.

1. I added a clustered index to your table. If you cannot do that, select the data into a temp table and add it to that. The clustered index is vital.

2. I added another field to hold the group information to your table. Same as the above.

3. The WITH (INDEX(0)) clause in the update can be modified to be WITH (INDEX(MyClusteredIndexName)) if you have multiple indexes on this table.

[font="Courier New"]IF OBJECT_ID('assembly_master_test','U') IS NOT NULL

DROP TABLE assembly_master_test

GO

CREATE TABLE [dbo].[assembly_master_test](

-- !!!NOTE!!! identifier is now a clustered index

[identifier] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,

[group_identifier] [int] NULL,

[id_model] [int] NOT NULL,

[model] [int] NOT NULL,

[node] [int] NOT NULL,

[checked] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,

[date] [datetime] NOT NULL,

[whereabout] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,

[text] [nvarchar](250) COLLATE Latin1_General_CI_AS NULL,

[version] [int] NOT NULL DEFAULT ((0)),

[contains_slave] [smallint],

GroupID INT)

GO

SET dateformat DMY;

SET IDENTITY_INSERT assembly_master_test ON;

INSERT INTO assembly_master_test

([identifier],[group_identifier],[id_model],[model],[node],[checked]

,[date],[whereabout],[text],[version],[contains_slave])

SELECT 1,1000,1,1,1,'myself',GETDATE(),'customer1','example',1,0 UNION ALL

SELECT 2,1001,20,2,1,'myself',GETDATE(),'customer1','example',1,0 UNION ALL

SELECT 3,1002,31,3,1,'myself',GETDATE(),'customer1','example',1,0 UNION ALL

SELECT 4,1003,18,7,1,'myself',GETDATE(),'customer2','example',1,0 UNION ALL

SELECT 5,1004,3,8,1,'myself',GETDATE(),'customer2','example',1,0 UNION ALL

SELECT 6,1005,28,11,1,'myself',GETDATE(),'customer3','example',1,0 UNION ALL

SELECT 7,1006,40,15,1,'myself',GETDATE(),'customer1','example',1,0 UNION ALL

SELECT 8,1007,4,16,1,'myself',GETDATE(),'customer1','example',1,0 UNION ALL

SELECT 9,1008,11,17,1,'myself',GETDATE(),'customer1','example',1,0

SET IDENTITY_INSERT assembly_master_test OFF;

------------------- Solution ------------------------------

DECLARE @GroupID INT,

@PrevCust VARCHAR(25),

@Id INT

SET @GroupID = 1

UPDATE assembly_master_test

SET @GroupID = GroupID = CASE WHEN @PrevCust = whereabout THEN @GroupID ELSE @GroupID + 1 END,

@PrevCust = whereabout,

@ID = Identifier

FROM assembly_master_test WITH (INDEX(0))

SELECT MIN(model) Model_From, MAX(model) Model_To, whereabout

FROM assembly_master_test

GROUP BY whereabout, GroupID

-----------------------------------------------------------[/font]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Greg Snidow SSCoach Points: 16108 More actions · Answer 7

Garadin (11/3/2008)
Trying... to... resist...
Gah.

Seth, I was rolling laughing at that. I'm a huge Dilbert fan, so any time I see 'Gah', it elicits strong images of Dilbert struggling against an invisible force of resistence eminating from the pointy-haired boss. The pauses between the words could not have been more appropriate. Thanks for making my day.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Garadin One Orange Chip Points: 29613 More actions · Answer 8

Greg Snidow (11/3/2008)
Garadin (11/3/2008)
Trying... to... resist...
Gah.
Seth, I was rolling laughing at that. I'm a huge Dilbert fan, so any time I see 'Gah', it elicits strong images of Dilbert struggling against an invisible force of resistence eminating from the pointy-haired boss. The pauses between the words could not have been more appropriate. Thanks for making my day.

Heh. Glad I could lighten up a Monday 🙂

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

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

Garadin (11/3/2008)
Trying... to... resist...
Gah.

BWAA-HAAAA!!! You're addicted! Now, that's some funny stuff right there! 🙂

Nice job on the code, Seth. Only thing you might want to try, just to be empirically correct, is start out with GROUP = 0... :hehe: Other than that, very well done!

--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)

Garadin One Orange Chip Points: 29613 More actions · Answer 10

Jeff Moden (11/3/2008)
Only thing you might want to try, just to be empirically correct, is start out with GROUP = 0...

Have you ever actually talked to a Group 1? They're all cheeky superior bastards who look down on everyone else. Group 2's on the other hand... those are the kind of guys I want in MY table.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 11

OMG! LMAO! Stop it! {Clutching at sides trying to catch a breath} That's just too funny! 😛

--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)

juergen.kessler SSC Rookie Points: 30 More actions · Answer 12

what a pity, as I'm not a native english (american) speaker, the obviously funny joke didn't hit me completely. 😀

Thank you, Seth.

Have a cigar!

Juergen

Greg Snidow SSCoach Points: 16108 More actions · Answer 13

I think they need to start a section for 'Best humor of the day', or maybe month. I gotta say, I'm not sure where I laughed harder...at this post, or the first time I saw the pork chop joke.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

sgmunson SSC Guru Points: 110640 More actions · Answer 14

I loved the Dilbert humor, but the moment it degenerated into a group discusson of number 1 and number 2 :D:D:D, it just wasn't all that funny any more...

Steve

(aka smunson)

:D:D:D

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)