Solved

Query Using Cross Something?

Posted on 2011-02-16
6
269 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MSSQL Speen Degradation 4 23
SQL 2008 with .NET 4.5.2 4 30
SQL Server 2012 r2 Make faster Temp Table 17 107
SQL Help 27 46
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

832 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