Solved

Query Using Cross Something?

Posted on 2011-02-16
6
263 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
  • 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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now