[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Query Using Cross Something?

Posted on 2011-02-16
6
Medium Priority
?
279 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

656 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