Prev: Display image from sql database
Next: SQL Error 22050. SQL2008
From: Peter Newman on 1 Aug 2010 20:11
SQL 2008

I have three tables which I need to get the following result from. Can
anyone help

CREATE TABLE [dbo].[tbBOSS_client](
[Licence] [varchar](6) NOT NULL,
[CompanyName] [varchar](256) NOT NULL,
[Status] [varchar](32) NULL,
CONSTRAINT [PK_tbBOSS_client] PRIMARY KEY CLUSTERED
(
[Licence] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tbBOSS_ClientService](
[Licence] [varchar](6) NOT NULL,
[ServiceID] [int] NOT NULL,
[AUDDISEnabled] [bit] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[tbBOSS_Service](
[ServiceID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](255) NOT NULL
) ON [PRIMARY]


INSERT INTO [TestDB].[dbo].[tbBOSS_client] ([Licence] ,[CompanyName]
,[Status])
VALUES ('217514', 'TEST COMPANY 1', 'ORDERED' )
INSERT INTO [TestDB].[dbo].[tbBOSS_client] ([Licence] ,[CompanyName]
,[Status])
VALUES ('217801', 'TEST COMPANY 2', 'Live' )

INSERT INTO [TestDB].[dbo].[tbBOSS_ClientService] ([Licence] ,[ServiceID]
,[AUDDISEnabled])
VALUES ('217514' ,1 ,0)
INSERT INTO [TestDB].[dbo].[tbBOSS_ClientService] ([Licence] ,[ServiceID]
,[AUDDISEnabled])
VALUES ('217514' ,3 ,1)
INSERT INTO [TestDB].[dbo].[tbBOSS_ClientService] ([Licence] ,[ServiceID]
,[AUDDISEnabled])
VALUES ('217514' ,6 ,0)
INSERT INTO [TestDB].[dbo].[tbBOSS_ClientService] ([Licence] ,[ServiceID]
,[AUDDISEnabled])
VALUES ('217801' ,2 ,0)
INSERT INTO [TestDB].[dbo].[tbBOSS_ClientService] ([Licence] ,[ServiceID]
,[AUDDISEnabled])
VALUES ('217801' ,4 ,0)

INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service1')
INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service2')
INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service3')
INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service4')
INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service5')
INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service6')
INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service7')

Result required

Licence Company Status Service1 Service2 Service3 Service4 Service5
Service6 Auddis
217514 TEST COMPANY 1 ORDERED 1 0 1 0 0 1 1
217801 TEST COMPANY 2 Live 0 1 0 0 0 0 0


From: Tom Cooper on 1 Aug 2010 23:21
One way. Note, your desired results did not include Service7, so I didn't
include it. If you meant to ask for it, just add it to the lines below that
reads

Select Licence, CompanyName, Status, Service1, Service2, Service3, Service4,
Service5, Service6, Audis
and
For Name In (Service1, Service2, Service3, Service4, Service5, Service6)

Also, your desired results show a value of 0 for service 4 for TEST COMPANY
2, I assumed you really wanted a value of 1 (since there is a row in
tbBOSS_ClientService for ServiceID 4 for that company). And that is what
the following code does. If I was incorrect, please let us know the
algorithym for deriving the values for ServiceN in your final result. I
assumed it was if a row for that company for the ServiceID existed, you
wanted a 1, otherwise 0.

With cte As
(Select c.Licence, c.CompanyName, c.Status, s.Name, Case When
cs.AUDDISEnabled Is Null Then 0 Else 1 End As ServiceValue
From dbo.tbBOSS_client c
Cross Join tbBOSS_Service s
Left Join tbBOSS_ClientService cs On c.Licence = cs.Licence And s.ServiceID
= cs.ServiceID)
Select Licence, CompanyName, Status, Service1, Service2, Service3, Service4,
Service5, Service6, Audis
From (Select cte.Licence, cte.CompanyName, cte.Status, cte.Name,
cte.ServiceValue, Max(Cast(cs.AUDDISEnabled As tinyint)) As Audis
From cte
Left Join tbBOSS_ClientService cs On cte.Licence = cs.Licence
Group By cte.Licence, cte.CompanyName, cte.Status, cte.Name,
cte.ServiceValue) As p
Pivot
(Max(ServiceValue)
For Name In (Service1, Service2, Service3, Service4, Service5, Service6)
) As pvt

Tom

P.S, thanks for providing the SQL Server version, DDL, and sample data.
That is very helpful.
"Peter Newman" <PeterNewman(a)discussions.microsoft.com> wrote in message
news:EA8F04E6-AC72-4AEB-BE8D-BE812B2250D8(a)microsoft.com...
> SQL 2008
>
> I have three tables which I need to get the following result from. Can
> anyone help
>
> CREATE TABLE [dbo].[tbBOSS_client](
> [Licence] [varchar](6) NOT NULL,
> [CompanyName] [varchar](256) NOT NULL,
> [Status] [varchar](32) NULL,
> CONSTRAINT [PK_tbBOSS_client] PRIMARY KEY CLUSTERED
> (
> [Licence] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [dbo].[tbBOSS_ClientService](
> [Licence] [varchar](6) NOT NULL,
> [ServiceID] [int] NOT NULL,
> [AUDDISEnabled] [bit] NULL
> ) ON [PRIMARY]
>
>
> CREATE TABLE [dbo].[tbBOSS_Service](
> [ServiceID] [int] IDENTITY(1,1) NOT NULL,
> [Name] [varchar](255) NOT NULL
> ) ON [PRIMARY]
>
>
> INSERT INTO [TestDB].[dbo].[tbBOSS_client] ([Licence] ,[CompanyName]
> ,[Status])
> VALUES ('217514', 'TEST COMPANY 1', 'ORDERED' )
> INSERT INTO [TestDB].[dbo].[tbBOSS_client] ([Licence] ,[CompanyName]
> ,[Status])
> VALUES ('217801', 'TEST COMPANY 2', 'Live' )
>
> INSERT INTO [TestDB].[dbo].[tbBOSS_ClientService] ([Licence] ,[ServiceID]
> ,[AUDDISEnabled])
> VALUES ('217514' ,1 ,0)
> INSERT INTO [TestDB].[dbo].[tbBOSS_ClientService] ([Licence] ,[ServiceID]
> ,[AUDDISEnabled])
> VALUES ('217514' ,3 ,1)
> INSERT INTO [TestDB].[dbo].[tbBOSS_ClientService] ([Licence] ,[ServiceID]
> ,[AUDDISEnabled])
> VALUES ('217514' ,6 ,0)
> INSERT INTO [TestDB].[dbo].[tbBOSS_ClientService] ([Licence] ,[ServiceID]
> ,[AUDDISEnabled])
> VALUES ('217801' ,2 ,0)
> INSERT INTO [TestDB].[dbo].[tbBOSS_ClientService] ([Licence] ,[ServiceID]
> ,[AUDDISEnabled])
> VALUES ('217801' ,4 ,0)
>
> INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service1')
> INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service2')
> INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service3')
> INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service4')
> INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service5')
> INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service6')
> INSERT INTO [TestDB].[dbo].[tbBOSS_Service] ([Name]) VALUES ('Service7')
>
> Result required
>
> Licence Company Status Service1 Service2 Service3 Service4 Service5
> Service6 Auddis
> 217514 TEST COMPANY 1 ORDERED 1 0 1 0 0 1 1
> 217801 TEST COMPANY 2 Live 0 1 0 0 0 0 0
>
>

 | 
Pages: 1
Prev: Display image from sql database
Next: SQL Error 22050. SQL2008