Help needed on 3 table query

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


0
Utf
8/2/2010 12:11:03 AM
sqlserver.programming 1873 articles. 0 followers. Follow

1 Replies
463 Views

Similar Articles

[PageSpeed] 4

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@discussions.microsoft.com> wrote in message 
news:EA8F04E6-AC72-4AEB-BE8D-BE812B2250D8@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
>
> 

0
Tom
8/2/2010 3:21:08 AM
Reply:

Similar Artilces:

Exchange Routing #3
I have Exchange 2k3 sp2 servers in the UK, Malaysia, and the US and India. Everything seemed to be working fine until last week until we received a couple of winmail.dat files here in the US from the UK. After examining the header info of the emails we discovered that email sent from the UK, bounced through either India or Malaysia on it's way here. I have routing group connectors set up to send the mail internally directly from one exchange server to the other. What would cause this to happen? Is this something that can be controlled through exchange? The only thing different bet...

Online/Offline Help
I use Microsoft Outlook 2003 with a combination of Exchange, IMAP, HTTP and POP3 mailboxes. Because my exchange server is behind a firewall I often just connect to the Internet for my IMAP, HTTP and POP3 accounts. Unfortunately when Outlook tries to connect, it cannot find the exchange server so it connects in offline mode. Is there anyway I can connect in online mode even though the one Exchange account may be offline? Are all services in one profile? Can you try using just the Exchange profile as a single profile and then the others as any combination that you like? Removing Exchange ...

Help with Abort Procedure
Good Morning, I have an event procedure that locks up when the copy and paste commands are used on the worksheet. I looked up some code I remembered from John Walkenbach's book "Excel 2000 Power Programming with VBA" about stopping endless loops and found the code for an AbortProc but I have been unable to make it work for my code. Could somebody let me know if I'm even on the right path or have I just not applied John's code correctly to my code. Also, is there a way using the AutoFit method to set a minimum column width to not go below? Thanks for the help. Here is m...

File in 2007 form
I was trying the Beta Office 2007 and made the mistake of saving one of you Excel file in the new 2007 form. I really need it back in the 2003 form. Problem: due to a variety of issues, I de-installed the Beta version and went back to 2003 but for got about the one file.. So; are there any utilities that anyone knows of the will convert this file back two 2003 form?? It is a simple Excel file, meaning that there are no clever or fancy formula or macros, just a collection of parts and part numbers gathered over MUCH time surfing various web sites. Any help or advise (beside the obvious....

Listbox Query Again
Sorry to ask again I have a problem with populating a listbox. I want to have the listbox populate using the current worksheet ranges a1:e1000. I know how to use the rowsource for individual list boxes but that would mean I would have to make many userforms up. I would rather have it run from 1 form. ANy advice would be great Thanks Greg Sorry I have worked that one out for myself Thanks Greg "Greg B" <laptopgb@ihug.com.au> wrote in message news:dgepep$1qg$1@lust.ihug.co.nz... > Sorry to ask again I have a problem with populating a listbox. I want to > ...

transfer emails from my Mac ( mail stamp figure ) to my computer Microsoft Outlook #3
How can I transfer my emails. ...

Query Q
I have a table called orders with 4 fields, Name, OrderNumber, StartTime, StopTime. I have 50 records in this table which represent 50 orders processed by a person in one day. I know how to do the time difference from the start time and the stop for an order, but how do I tell the time difference of the stop time for order 1 and the start time of order 2? Stop time of order 2 and the start time of order 3 and so on? Thanks, Sean SELECT a.orderID, LAST(a.stopTime), MIN(b.startTime) FROM myTable AS a LEFT JOIN myTable AS b ON a.stopTime < b.startTime GROUP BY a.orderID should...

Why access add blank record in table
Greeting, I have a table without Primary key. When I want to add new records in this table, I open form to do that. The problem is when I decided to exit this form without adding new records; a blank record has been added to the table. Note: there is a macro in on load of the form event to reset the value of all textboxes and combos. Any help please Hi Jon, > I have a table without Primary key. That's your first mistake. Every table *should* have a primary key (or, as a minimum, a uniquely indexed field). > The problem is when I decided to exit this > form without addi...

Everytime I open an Office application I get an error message, please help!!!
Hi I've had office for my mac for about 4 months, at some point in the last 2 weeks everytime I try to open word, or excell I get an error message "the program has unexpectly quit" any idea or suggestions please help! Hi Dan, It will help us help you if you post back with some additional information. What version of Office? What version of MacOS? Have you installed any new fonts or software that installs foonts lately? -Jim -- Jim Gordon Mac MVP MVP FAQ <http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs> Dan Goldberg wrote: > Hi > I'v...

The MAPI call 'OpenMsgStore' failed with the following error #3
I 'v got this message after installing Exchange 2003 server. Everythings seems to be allright under setup, but when I go to the Application logg the message under comes every minute. The MAPI call 'OpenMsgStore' failed with the following error: The Microsoft Exchange Server computer is not available. Either there are network problems or the Microsoft Exchange Server computer is down for maintenance. The MAPI provider failed. Microsoft Exchange Server Information Store ID no: 8004011d-0526-00000000 I'v checked many internett sites but didn't find any good solution. ...

help 02-09-06
Who wants to help me with the subnetworking Ex: IP : 14.0.0.0. Used Subnets 10 Nr Hosts = ? Mask ? The 3-th Subnet ? The defaul brodcast ? Sorry, you'll need to find a more appropriate newsgroup in which to ask this question, one that deals with networking. This newsgroup is for questions about Access, the database product that's part of Office Professional. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Cuparencu Virgil" <Cuparencuv2003@yahoo.co.uk> wrote in message news:%237HgORLLGHA.2768@TK2MSFTNGP10.phx.gbl... &...

Time format #3
I have a cell which is formatted to time as hh:mm Now, the user has to type the time in the above format, hh:mm as above. Is it possible for me to allow the user to simply type in the time as hhmm and then for the spreadsheet to automatically format it to hh:mm. So for example, 08:30. I would like the user to be able to type in either 08:30 or 0830 and for the spreadsheet to recognise both entries and format it to 08:30. Any ideaS? Neil Not without using VBA See Chip Pearson's site for code. http://www.cpearson.com/excel/DateTimeEntry.htm Gord Dibben Excel MVP On Sun, 27 N...

I Really need help with formula
I have CK4 Min. hours & CL4 Max hours & CM4 is going to = Differece between CK4 & CL4, CG5:CG65 =names & CJ5:CJ65 =hours remember hours & names belong together, Now what i want is to have Names transfered over to CK5:CK65 if greater than 28 from Min to Max & leave cells blank if less Also in CG5:CG65 for names i have this formula =INDIRECT("B" & VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE)) & I have a #NA only until names are entered in other cells So in CK5:CK65 IF LESS THAN 28 lEAVE IT BLANK see your MIN/MAX post -- Biff Microsoft Excel ...

Repost from Jan 3
I've reposted some tips regarding grouping on the service calendar. Dave's tips below were very useful and I had a question regarding them and figured no one would find it waaay back on Jan 3! Question: Have you found a way to set a particular view as default? E.g., Type = User, View = Users in a Team Also, in case this helps anyone else - I slightly modified Dave's approach below which will hopefully reduce the amount of maintaining 2 team lists as he mentions. What I did was create an attribute (I called it "Team") and added it to the main user form. Then, fo...

Form Add-On or feature with explorer (help please)
Good Morning Folks, I am trying to find either an add-in or code to automatically put a file explorer inside an Access form. I would like to browse thru a database table that contains a directory and the file explorer displays the contents (files and folders) in a form window. Other things I would like to do inside of the file explorer is double click word docs to open or move thru the directories inside of the file explorer. Any help would be greatly appreciated. Thank you in advance, Mike ...

need macro ideas for storing report sheets to workbook
HI!! thanks for helping!! Can anyone give me an idea(s) to write a macro that will take my current report ( a template)for the day and purge (store) say... to "workbook 1" , "page one" and then renew the report sheet for new data and then store it next to the previous page in the same worksheet as i create a report for each day. I will be doing 540 days worth of reports ( text and data) and would like it under the same workbook. If not at all impossible....have it sort or file the reports by its date of inputinto its designated worksheet by month.. example: reports ...

problem importing accounts into crm 3.0 through th bulk import wiz
We are trying to import accounts records into CRM 3.0 through the application's bulk import wizard (tools --> import). the source file is a text file, which contains small amount of data. the process is going well, and being finished successfully, without any warnings or error messages. but the accounts records aren't being imported. when we go to Workplace --> Activities --> Bulk import, to check the status of our import, we can see our import record, but with a Status Reason 'pending'. (is not executed?) there are no start or finished indications, and no fail...

How can I time long running queries?
Hello all, Is it possible to time long running select queries? I was looking at creating some code to do this....something like: 1. Store current time in a time variable 2. Try to programmatically open a query ("open" meaning just like manually opening a select query from MS Access to view data) using "Application.CurrentDb.QueryDefs("Query3").OpenRecordset dbOpenTable". 3. Check the current time and compare it to the start time saved at the beginning But, I get "Run-time error '3219': Invalid operation." when I run "...OpenRecord...

How do I get Publisher 2003 to recognize all the queries in Acces.
How do I get Publisher 2003 to recognize all the queries in Access 97? When I try to mail merge, I can only see half the queries and never the one I want. There doesn't seem to be anywhere to make selecting dde as an option like there is in word. A small child turns to Ed, and exclaims: "Look! Look! A post from rebs <rebs@discussions.microsoft.com>!"... > How do I get Publisher 2003 to recognize all the queries in Access > 97? When I try to mail merge, I can only see half the queries and > never the one I want. There doesn't seem to be anywhere to make ...

Friends, Help me create a digital signature please.
Hello friends! Can you please help me create a digital signature for all my mails? Thank you. Cheers! Salma J. Khan Not enough information to go on, but if you are trying to find a vendor that offers free personal certificates so you can do s/mime, then see: https://www.thawte.com/secure-email/personal-email-certificates/index.html "Salma J. Khan" <Salma J. Khan@discussions.microsoft.com> wrote in message news:4F26FBBC-EC07-4DE7-949A-327B9061F352@microsoft.com... > Hello friends! > > Can you please help me create a digital signature for all my mails? > >...

ObjectTypeCode in Entity table
Do you know the function of ObjectTypeCode attribute in Entity table in _metabase db? I have noticed that for some records the relative value is 0, for a CRM 1.2 installation, but not for the same table for a CRM 3.0. Many thanks The ObjectTypeCode determines the type of entity in 3.0. For example, 1 = account, 2 = contact. This code appears throughout the application as a quick and easy way to identify the type of entity being referred to. A complete mapping can be found by running the following query select objecttypecode, name from _metabase.dbo.entity order by objecttypecode ...

Messenger 80040154 error
I've tried all as suggested here... http://help.live.com/help.aspx?mkt=en-us&project=WL_Messengerv9&querytype=keyword&query=80040154&Product=2&Version=14.0.8089.0726&ErrorCode=80040154&ErrorId=80040154&Locale=en_ms ....still can't sign in. Please help. Please! I know many are having this issue, so I hope for a quick fix if someone can jump in. Thanks "Ashley Smith" wrote: > I've tried all as suggested here... > > http://help.live.com/help.aspx?mkt=en-us&project=WL_Messengerv9&querytype=keyword&am...

Border #3
I have found some clipart for a letter with a line of pilgrims. How do I insert the line of clipart. It seems to be somewhat difficult to copy and paste and to try to fit each section. I am using a footer and a header and a temporaty page border and then rotating other sections to go along the sides, but they are overlapping. I tried align and distribute horizontally and vertically, but it isn't working very well. Try this newsgroup: news://msnews.microsoft.com/microsoft.public.word.pagelayout or any Word newsgroup will probably get you an answer. -- Mary Sauer MS MVP http://of...

Data sort, > 3 columns
Hi Excel has the Data sort option. Data>> Sort This allows you to sort 3 columns Is it possible to sort more than 3 columns?? I would appreciate any advice. Thanks Clive This link explains how to do it: http://www.contextures.com/xlSort01.html#Sort04 Basically, you sort on the least important fields first, and then sort on the more important fields. Alternatively, you can combine some fields together in a helper column. Hope this helps. On Jun 4, 11:04=A0am, Clive_S <clives...@yahoo.co.uk> wrote: > Hi > > Excel has the Data sort option. > > Data>> S...

Error When deleting from CRM 3.0
All of a sudden I cannot delete any data from CRM 3.0. I recieve an error that states "SQL Server Error Happened. Please contact an administrator" I cannot find any information in the Event viewer. Has anyone seen this problem or know of a solution. Any help is greatly appreciated. Thanks, Lee I have equal problem. already does someone have a solution for it? Thanks... "Lee" schrieb: > All of a sudden I cannot delete any data from CRM 3.0. I recieve an error > that states "SQL Server Error Happened. Please contact an administrator" I > c...