March 17, 2009 at 10:50 pm
Hello Guys,
My colleague is using a code gen and as a result has set the edit form to accept multiple selections from a lookup table.
I am Attempting to build a view for reporting purposes and have encountered this problem
Problem:
In the ssmse app on the design surface I have a data table and a number of lookups (pk and fk relations) the issue is that all the fields have multiple values in them eg 1,57,42 so I need a way to break these up on the fly so that the IDs still match that of the look up and the customer
eg:
current
CustomerID CustomerName ProjectTypeid ProjectTypeName(lookup table)
1 John smith 1 Preannials
1 John smith 1,57,42 error
Required
CustomerID CustomerName ProjectTypeid ProjectTypeName
1 John smith 1 Preannials
1 John smith 57 Bio Banking
1 John smith 42 Soil Conservation
I hope this makes sense
SELECT dbo.customers_data_0_enquiries.EOI_Enquiry_ID, dbo.customers_data_0_enquiries.CustomerID, dbo.customers_data_0_enquiries.Information_Already,
dbo.customers_data_0_enquiries.CustomerStatus, dbo.eoi_lut_status_enquiry.EOI_Enquiry_Status, dbo.project_lut_project_type.Project_Type_Name,
dbo.eoi_lut_status_pre_training.TrainingStatus, dbo.customers_data_0_enquiries.EOI_Enquiry_Notes, dbo.customers_data_0_enquiries.Mailout_Requested,
dbo.customers_data_0_enquiries.MailoutSentDate, staff_data_0_staff_list_3.FirstName, dbo.customers_data_0_enquiries.NewsletterRequested,
dbo.customers_data_0_enquiries.NewsletterSentDate, staff_data_0_staff_list_1.FullName, dbo.eoi_lut_eoi_enquiry_survey.IPSurveyTypes,
dbo.eoi_lut_eoi_enquiry_survey_subtypes.IPSurveySubType, dbo.customers_data_1_eoi.EOI_Application_ID, dbo.staff_data_0_staff_list.FullName AS Expr1,
dbo.customers_data_1_eoi.EOI_Received_Date, dbo.eoi_lut_eoi_type.EOI_Type, dbo.eoi_lut_status.EOI_Status, staff_data_0_staff_list_2.FullName AS Expr2,
dbo.customers_data_1_eoi.EOI_DateAllocated, dbo.customers_data_1_eoi.IsGroupBid, dbo.customers_data_0_details.Name_Contact,
dbo.customers_data_1_eoi.GroupBidNotes, dbo.customers_data_1_eoi.ProposedProjectDescription, dbo.customers_data_1_eoi.ProposedProjectAchievements,
dbo.eoi_lut_eoi_risk_assessment.RiskAsessmentStatus, dbo.customers_data_1_eoi.RiskAsessmentNotes, dbo.customers_data_1_eoi.PreviousFunding_Declaration,
dbo.customers_data_1_eoi.PreviousFunding_Details, dbo.customers_data_1_eoi.CurrentFunding_Declaration, dbo.customers_data_1_eoi.CurrentFunding_Details,
dbo.customers_data_1_eoi.NVA_Declaration, dbo.customers_data_1_eoi.NVA_Details, dbo.customers_data_1_eoi.PVPOffset_Declaration,
dbo.customers_data_1_eoi.PVPOffset_Details, dbo.customers_data_1_eoi.OtherFunding, dbo.customers_data_1_eoi.OtherFundingVerified,
dbo.customers_data_1_eoi.OtherFundingNotes, dbo.customers_data_1_eoi.ApplicationSigned, dbo.customers_data_1_eoi.ApplicantNames,
dbo.customers_data_1_eoi.ProjectsOutstanding, dbo.customers_data_1_eoi.ProjectsOutstandingNotes, dbo.customers_data_1_eoi.ApplicationDated
FROM dbo.project_lut_project_type RIGHT OUTER JOIN
dbo.eoi_lut_eoi_risk_assessment RIGHT OUTER JOIN
dbo.eoi_lut_status RIGHT OUTER JOIN
dbo.eoi_lut_status_pre_training RIGHT OUTER JOIN
dbo.staff_data_0_staff_list AS staff_data_0_staff_list_1 RIGHT OUTER JOIN
dbo.customers_data_0_details RIGHT OUTER JOIN
dbo.eoi_lut_eoi_type RIGHT OUTER JOIN
dbo.customers_data_1_eoi ON dbo.eoi_lut_eoi_type.EOI_Type_ID = dbo.customers_data_1_eoi.EOI_Type_ID LEFT OUTER JOIN
dbo.staff_data_0_staff_list ON dbo.customers_data_1_eoi.EOI_Entry_Officer_UserID = dbo.staff_data_0_staff_list.UserID RIGHT OUTER JOIN
dbo.shared_lut_cma_nsw_names ON dbo.staff_data_0_staff_list.CMAID = dbo.shared_lut_cma_nsw_names.CMAID ON
dbo.customers_data_0_details.CMAID = dbo.shared_lut_cma_nsw_names.CMAID AND
dbo.customers_data_0_details.CustomerID = dbo.customers_data_1_eoi.Group_Coordinator_CustomerID LEFT OUTER JOIN
dbo.staff_data_0_staff_list AS staff_data_0_staff_list_2 ON dbo.shared_lut_cma_nsw_names.CMAID = staff_data_0_staff_list_2.CMAID AND
dbo.customers_data_1_eoi.Coordinator_UserID = staff_data_0_staff_list_2.UserID ON
staff_data_0_staff_list_1.CMAID = dbo.shared_lut_cma_nsw_names.CMAID RIGHT OUTER JOIN
dbo.customers_data_0_enquiries ON dbo.customers_data_1_eoi.EOI_Enquiry_ID = dbo.customers_data_0_enquiries.EOI_Enquiry_ID AND
staff_data_0_staff_list_1.UserID = dbo.customers_data_0_enquiries.NewsletterSentBy_UserID LEFT OUTER JOIN
dbo.staff_data_0_staff_list AS staff_data_0_staff_list_3 ON dbo.shared_lut_cma_nsw_names.CMAID = staff_data_0_staff_list_3.CMAID AND
dbo.customers_data_0_enquiries.MailoutSentBy_UserID = staff_data_0_staff_list_3.UserID ON
dbo.eoi_lut_status_pre_training.TrainingStatusID = dbo.customers_data_0_enquiries.TrainingStatusID ON
dbo.eoi_lut_status.EOI_Status_ID = dbo.customers_data_1_eoi.EOI_Status_ID ON
dbo.eoi_lut_eoi_risk_assessment.RiskAsessmentStatusID = dbo.customers_data_1_eoi.RiskAsessmentStatusID ON
dbo.project_lut_project_type.ProjectType_ID = dbo.customers_data_0_enquiries.ProjectType_ID AND
dbo.project_lut_project_type.CMAID = dbo.shared_lut_cma_nsw_names.CMAID LEFT OUTER JOIN
dbo.eoi_lut_eoi_enquiry_survey ON dbo.shared_lut_cma_nsw_names.CMAID = dbo.eoi_lut_eoi_enquiry_survey.CMAID AND
dbo.customers_data_0_enquiries.IPSurveyID = dbo.eoi_lut_eoi_enquiry_survey.IPSurveyID LEFT OUTER JOIN
dbo.eoi_lut_eoi_enquiry_survey_subtypes ON dbo.shared_lut_cma_nsw_names.CMAID = dbo.eoi_lut_eoi_enquiry_survey_subtypes.CMAID AND
dbo.eoi_lut_eoi_enquiry_survey.IPSurveyTypes = dbo.eoi_lut_eoi_enquiry_survey_subtypes.IPSurvey_SubTypes AND
dbo.customers_data_0_enquiries.IPSurvey_SubTypes = dbo.eoi_lut_eoi_enquiry_survey_subtypes.IPSurvey_SubTypes LEFT OUTER JOIN
dbo.eoi_lut_status_enquiry ON dbo.customers_data_0_enquiries.EOI_Enquiry_Status_ID = dbo.eoi_lut_status_enquiry.EOI_Enquiry_Status_ID
March 18, 2009 at 3:08 am
A perfect article about on the fly/high performance splitting would be this article of Jeff Moden:
http://www.sqlservercentral.com/articles/TSQL/62867/
Greets
Flo
March 18, 2009 at 7:02 am
This might sound like am absolutely ridiculous question.. but as I am a newbie to SQL Syntax (I am native C#) I really have trouble understanding that article I understand the fact I could create a 'Tally Table' but will this interfere with the relationships of the DB and also the 'Tally Table' technique loops until it runs out of commas what happens if the next record is customer 1 ProjectsTypeID 1,2,3 and customer 2 ProjectsTypeID 1 so wouldn't the 'Tally Table' stop looping once it gets to customer 2??
and it needs to link to the table and maintain referential integrity so the data comes back correct
"Anyone who has never made a mistake has never tried anything new."
-Albert Einstein-
March 18, 2009 at 1:29 pm
The question is absolutely not ridiculous 🙂
Sorry I didn't read correct and notice that you want to use this on a whole table. In this case I only know a way over a loop. Hopefully somebody else has a better way...
This sample creates a result table @cust_proj.
SET NOCOUNT ON
-- your customer
DECLARE @cust TABLE (id INT, name VARCHAR(100), proj_type VARCHAR(100))
INSERT INTO @cust VALUES (1, 'cust1', '1,2,3')
INSERT INTO @cust VALUES (2, 'cust2', '3')
-- your projects
DECLARE @proj TABLE (id INT, name VARCHAR(100))
INSERT INTO @proj VALUES (1, 'proj1')
INSERT INTO @proj VALUES (2, 'proj2')
INSERT INTO @proj VALUES (3, 'proj3')
-- The transformed result table
DECLARE @cust_proj TABLE (id INT, cust_name VARCHAR(100), proj_type INT, proj_name VARCHAR(100))
-- A tally table
DECLARE @tally TABLE (n INT, PRIMARY KEY (n))
INSERT INTO @tally
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY sc1.object_id)
FROM master.sys.columns sc1
CROSS JOIN master.sys.columns sc2
DECLARE @prev_id INT
DECLARE @id INT
DECLARE @proj_type VARCHAR(100)
-- Set any id which is less than smalles possible customer id
SET @prev_id = -1
-- Maybe use this if not sure:
--SELECT @prev_id = MIN(id) - 1 FROM @cust
-- Start the loop
WHILE (1 = 1)
BEGIN
SET @id = NULL
-- Get next customer id and proj_type
SELECT TOP(1) @id = id,
@proj_type = ',' + proj_type + ',',
@prev_id = @id
FROM @cust WHERE id > @prev_id
ORDER BY id
-- If no more customers; break
IF (@id IS NULL)
BREAK
-- Split the customer information
INSERT INTO @cust_proj (id, proj_type)
SELECT @id, SUBSTRING(@proj_type,N+1,CHARINDEX(',',@proj_type,N+1)-N-1)
FROM @tally
WHERE n < LEN(@proj_type)
AND SUBSTRING(@proj_type,N,1) = ','
END
-- Get the other customer information in one batch
UPDATE cp SET cp.cust_name = c.name
FROM @cust_proj cp
JOIN @cust c ON cp.id = c.id
-- Get the other project information in one batch
UPDATE cp SET cp.proj_name = p.name
FROM @cust_proj cp
JOIN @proj p ON cp.proj_type = p.id
-- Result table
SELECT * FROM @cust_proj
Greets
Flo
March 19, 2009 at 1:25 pm
Hi trend
It completely bothered me what I gave you as solution. So I practiced my self to find a good way. 🙂
Try this (this should be a much better solution):
IF (OBJECT_ID('dbo.ufn_split_string') IS NOT NULL)
DROP FUNCTION dbo.ufn_split_string
GO
CREATE FUNCTION dbo.ufn_split_string (@txt NVARCHAR(MAX), @delimiter NVARCHAR(10))
RETURNS @result TABLE (splited NVARCHAR(MAX))
AS
BEGIN
SELECT @txt = @delimiter + @txt + @delimiter
INSERT INTO @result
SELECT SUBSTRING(@txt, N + 1, CHARINDEX(@delimiter, @txt, N + 1) - N - 1)
FROM dbo.Tally
WHERE N < LEN(@txt)
AND SUBSTRING(@txt, N, 1) = @delimiter
RETURN
END
GO
-- some test customer
;WITH cust (id, name, proj_type) AS
(
SELECT 1, 'cust1', '1,2,3'
UNION ALL SELECT 2, 'cust2', '3'
), -- some test projects
proj (id, name) AS
(
SELECT 1, 'proj1'
UNION ALL SELECT 2, 'proj2'
UNION ALL SELECT 3, 'proj3'
)
SELECT *
FROM cust c
CROSS APPLY dbo.ufn_split_string(c.proj_type, ',') cp
JOIN proj p ON p.id = cp.splited
Now I'm feelin' better. 😀
Greets
Flo
(Thanks to Bob for some hints!!)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply