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