[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Select SQL Script in 2005

RickSean

3/20/2007 8:05:00 PM

CREATE TABLE [dbo].[Category](
[CatID] [nvarchar](16) NOT NULL,
[CatNO1] [smallint] NOT NULL,
[CatNO2] [smallint] NOT NULL
CONSTRAINT [Category$PrimaryKey] PRIMARY KEY CLUSTERED
(
[CatID] ASC,
[CatNO1] ASC,
[CatNO2] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
insert into dbo.Category values('12345', 1, 2)
insert into dbo.Category values('12345', 2, 3)
insert into dbo.Category values('12345', 3, 1)
insert into dbo.Category values('12345', 4, 2)
insert into dbo.Category values('52345', 1, 4)
insert into dbo.Category values('52345', 2, 1)
insert into dbo.Category values('52345', 3, 2)


CREATE TABLE [dbo].[Description](
[CatID] [nvarchar](16) NOT NULL,
[CatNO] [smallint] NOT NULL,
[CatDesc] [nvarchar](16) NOT NULL,
CONSTRAINT [Description$PrimaryKey] PRIMARY KEY CLUSTERED
(
[CatID] ASC,
[CatNO] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
insert into dbo.Description values('12345', 1, 'Desc1')
insert into dbo.Description values('12345', 2, 'Desc2')
insert into dbo.Description values('12345', 3, 'Desc3')
insert into dbo.Description values('12345', 4, 'Desc4')
insert into dbo.Description values('12345', 5, 'Desc5')
insert into dbo.Description values('52345', 1, 'DescA')
insert into dbo.Description values('52345', 2, 'DescB')
insert into dbo.Description values('52345', 3, 'DescC')
insert into dbo.Description values('52345', 4, 'DescD')
insert into dbo.Description values('52345', 5, 'DescE')

I need a query to create a table and return records as:
CREATE TABLE [dbo].[Category_New](
[CatID] [nvarchar](16) NOT NULL,
[CatNO1] [smallint] NOT NULL,
[CatNo1Desc] [nvarchar](16) NOT NULL,
[CatNO2] [smallint] NOT NULL
[CatNo2Desc] [nvarchar](16) NOT NULL,
CONSTRAINT [Category$PrimaryKey] PRIMARY KEY CLUSTERED
(
[CatID] ASC,
[CatNO1] ASC,
[CatNO2] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CatID CatNo1 CatNo1Desc CatNo2 CatNo2Desc
12345 1 Desc1 2 Desc2
12345 2 Desc2 3 Desc3
---------
52345 1 Desc1 4 Desc4
52345 2 Desc2 1 Desc1
2 Answers

Hugo Kornelis

3/21/2007 12:11:00 AM

0

On Tue, 20 Mar 2007 13:05:26 -0700, RickSean wrote:

(snip)
>I need a query to create a table and return records as:
(snip)

Hi Rick,

Thanks for posting CREATE TABLE and INSERT statements and expected
results; this made it easy to write, test and debug the following query,
which does exactly what you want:

SELECT c.CatID,
c.CatNO1, d1.CatDesc AS CatNo1Desc,
c.CatNO2, d2.CatDesc AS CatNo2Desc
FROM Category AS c
INNER JOIN Description AS d1
ON d1.CatID = c.CatID
AND d1.CatNO = c.CatNO1
INNER JOIN Description AS d2
ON d2.CatID = c.CatID
AND d2.CatNO = c.CatNO2;


--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

RickSean

3/21/2007 1:54:00 PM

0

Hugo,
I thank you for the script; i would need to implement this in to my complex
quey and test. I will update accordingly.


"Hugo Kornelis" wrote:

> On Tue, 20 Mar 2007 13:05:26 -0700, RickSean wrote:
>
> (snip)
> >I need a query to create a table and return records as:
> (snip)
>
> Hi Rick,
>
> Thanks for posting CREATE TABLE and INSERT statements and expected
> results; this made it easy to write, test and debug the following query,
> which does exactly what you want:
>
> SELECT c.CatID,
> c.CatNO1, d1.CatDesc AS CatNo1Desc,
> c.CatNO2, d2.CatDesc AS CatNo2Desc
> FROM Category AS c
> INNER JOIN Description AS d1
> ON d1.CatID = c.CatID
> AND d1.CatNO = c.CatNO1
> INNER JOIN Description AS d2
> ON d2.CatID = c.CatID
> AND d2.CatNO = c.CatNO2;
>
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hug...
>