Link to home
Start Free TrialLog in
Avatar of MariaHalt
MariaHaltFlag for United States of America

asked on

Query Using Cross Something?

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.  User generated image
Avatar of MariaHalt
MariaHalt
Flag of United States of America image

ASKER

Just read up on cross join, that's not it.
Avatar of cyberkiwi
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.
cyberkiwi:  number of service ids can vary
cybekiwi:  Tried it, am getting the following error:  Invalid object name 'dbo.values2table'.
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks...adding a bunch of F fields was exactly what I was going to do!