compressing / collapsing data in select

[drjaykay]

Hello All,
below you find SQL for 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','examp le',1,0 UNION ALL
select 0,1001,20,2,1,'myself',getdate(),'customer1','exam ple',1,0 UNION ALL
select 0,1002,31,3,1,'myself',getdate(),'customer1','exam ple',1,0 UNION ALL
select 0,1003,18,7,1,'myself',getdate(),'customer2','exam ple',1,0 UNION ALL
select 0,1004,3,8,1,'myself',getdate(),'customer2','examp le',1,0 UNION ALL
select 0,1005,28,11,1,'myself',getdate(),'customer3','exa mple',1,0 UNION ALL
select 0,1006,40,15,1,'myself',getdate(),'customer1','exa mple',1,0 UNION ALL
select 0,1007,4,16,1,'myself',getdate(),'customer1','exam ple',1,0 UNION ALL
select 0,1008,11,17,1,'myself',getdate(),'customer1','exa mple',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 ---------------------------------------------

[Christian Marquardt]

Irgendwie wäre ein Beispiel mit Inhalt besser als SQLs dur die man durch muss

[drjaykay]

Irgendwie wäre ein Beispiel mit Inhalt besser als SQLs dur die man durch muss

Ok, hier das Ergebnis des Scripts: (siehe auch xls im Anhang)

identifier group_identifier id_model model node checked date whereabout text version contains_slave
0 1000 1 1 1 "myself" "03.11.2008 11:00" "customer1" "example" 1 0
0 1001 20 2 1 "myself" "03.11.2008 11:00" "customer1" "example" 1 0
0 1002 31 3 1 "myself" "03.11.2008 11:00" "customer1" "example" 1 0
0 1003 18 7 1 "myself" "03.11.2008 11:00" "customer2" "example" 1 0
0 1004 3 8 1 "myself" "03.11.2008 11:00" "customer2" "example" 1 0
0 1005 28 11 1 "myself" "03.11.2008 11:00" "customer3" "example" 1 0
0 1006 28 15 1 "myself" "03.11.2008 11:00" "customer1" "example" 1 0
0 1007 4 16 1 "myself" "03.11.2008 11:00" "customer1" "example" 1 0
0 1008 11 17 1 "myself" "03.11.2008 11:00" "customer1" "example" 1 0