Solved

Query Using Cross Something?

Posted on 2011-02-16
6
274 Views
Last Modified: 2012-05-11
Given these 2 tables:

CREATE TABLE tempdb.dbo.mh_Services (ServiceID INT NOT NULL, ServiceTypes NVARCHAR(255) NOT NULL)

INSERT INTO tempdb.dbo.mh_Services VALUES (1, '100')
INSERT INTO tempdb.dbo.mh_Services VALUES (2, '101, 102, 105')
INSERT INTO tempdb.dbo.mh_Services VALUES (3, '100, 105')

CREATE TABLE tempdb.dbo.mh_ServiceTypes (
ServiceType INT NOT NULL,
ServiceType_Description NVARCHAR(255) NOT NULL)
INSERT INTO tempdb.dbo.mh_ServiceTypes VALUES (100, 'Oil Change')
INSERT INTO tempdb.dbo.mh_ServiceTypes VALUES (101, 'Filter Change')
INSERT INTO tempdb.dbo.mh_ServiceTypes VALUES (102, 'Hydraulic Fluid Change')
INSERT INTO tempdb.dbo.mh_ServiceTypes VALUES (103, 'Windshield Fluid Replacement')
INSERT INTO tempdb.dbo.mh_ServiceTypes VALUES (104, 'Windshield Wiper Replacement')
INSERT INTO tempdb.dbo.mh_ServiceTypes VALUES (105, 'Belt Replacement')

Need a query that will spit out results just like in the attachment, without using dynamic sql.   I'm thinking of using cross join or cross apply but don't have time to delve into learning all their specifics right now.  Ppplease help.  Thanks.  Query Results
0
Comment
Question by:MariaHalt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 

Author Comment

by:MariaHalt
ID: 34910224
Just read up on cross join, that's not it.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34910243
If you have a maximum of only 3 values, then you can use the below.

select *
from 
(
select
	s.ServiceID, s.ServiceTypes, t.ServiceType_Description,
	F = 'F' + right(ROW_NUMBER() over (partition by s.serviceID order by v.value),10)
from mh_Services s
cross apply dbo.values2table(s.ServiceTypes,',',-1) v
inner join mh_ServiceTypes t on t.ServiceType = v.value
) P
pivot (max(ServiceType_Description) for F in ([F1],[F2],[F3])) v

Open in new window


Any more than 3 means you need to add to the [F1],[F2],[F3] part on the last line.
0
 

Author Comment

by:MariaHalt
ID: 34910348
cyberkiwi:  number of service ids can vary
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:MariaHalt
ID: 34910384
cybekiwi:  Tried it, am getting the following error:  Invalid object name 'dbo.values2table'.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34912861
Ah. you need this function

CREATE function dbo.values2table
(
@values varchar(max),
@separator varchar(3),
@limit int -- set to -1 for no limit
) returns @res table (id int identity, [value] varchar(max))
as
begin
declare @value varchar(50)
declare @commapos int, @lastpos int
set @commapos = 0
select @lastpos = @commapos, @commapos = charindex(@separator, @values, @lastpos+1)
while @commapos > @lastpos and @limit <> 0
begin
	select @value = substring(@values, @lastpos+1, @commapos-@lastpos-1)
	if @value <> '' begin
		insert into @res select ltrim(rtrim(@value))
		set @limit = @limit-1
	end
	select @lastpos = @commapos, @commapos = charindex(@separator, @values, @lastpos+1)
end
select @value = substring(@values, @lastpos+1, len(@values))
if @value <> '' insert into @res select ltrim(rtrim(@value))
return
end
GO

Open in new window


It only needs to be created once.

> cyberkiwi:  number of service ids can vary

Other than using dynamic SQL there is no way for a query to return an indeterminate number of columns. You are better off making it go up to [F10] for example, and just ignore columns with no data
0
 

Author Closing Comment

by:MariaHalt
ID: 34918316
Thanks...adding a bunch of F fields was exactly what I was going to do!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question