August 29, 2010 at 10:37 pm
Hi
I have a table Customer, which has all the customer details.
and which as columns
CustID,CustName,CustAddress1,Custcontact....etc
Ex 1 abc test12345
2 xyz test134567
3 uvw test3 67809
and have another table which maintains the Customer survery details...one customer can have one or more surveys.
Customer Survey table.
CustiD SurveyID SurveyDate .....etc
1 1 01/01/2010
1 2 02/02/2010
2 3 03/03/2010
2 4 04/04/2010
2 5 05/05/2010
3 6 06/06/2010
3 7 07/07/2010
3 8 08/08/2010
3 9 09/09/2010
and have another table which maintains all the question and answer details for each survery.
Custid QuestionID AnswerID SurveryID
1 10 11 1
1 90 12 2
1 80 13 3
2 50 45 1
2 60 15 2
3 40 18 1
3 50 15 2
3 70 13 3
and I have 2 additional lookup tables
Questiontable ( which has all the question ID's and description of the Question)
ANswertable (Which has all the answer id's and description of the answers)
I will explain in detail about the requirement.
I work for a small client and which has around 1000 customers. For each customer we do a survey and we ask questions and customers give answers for each question.we have like 50 questions for each customer and depends on the customer purchases...
we might ask only 20 out of 50 questions for first customer and for second customer we might ask only 40 questions out of 50 questions and for third customer we might ask all the 50 questions....
My requirement is to display the columns from the above tables in just one row.....
CustID CustName Custaddress CustContact Surveyid(First) SurveryDate QuestionID1 AnswerID1 QuestionID2 Answerid2 ....NQuestionID NAnswerID SurveyID(Second) SurveryDate QuestionID1 AnswerID1 QuestionID2 ANswerID2 ....NQuestionID's NAnswerID SurveyID(Third) SurveryDate QuestionID1 AnswerID1 QuestionID2 ANswerID2 ....NQuestionID's
Notes
First Row CustID1 with first survey ID,Survey Date and questions asked and answers given by customers...Second survery ID,Survey Date and Question Asked answers given by customers....Recent Survey ID,Survey Date and Questions asked answers given by customers....(if they dont have any third survey..just want to display the first 2 survey details and third survey details as Null).
Second Row Customer 2 with first survey ID,Survey Date and questions asked and answers given by customers...Second survery ID,Survey Date and Question Asked answers given by customers....Recent Survey ID,Survey Date and Questions asked answers given by customers....(if they dont have any third survey..just want to display the first 2 survey details and third survey details as Null).
I am doing a self join and i get the results in 3 different rows...but i need all the 3 survey details in one row...even though they are stored as 3 rows in the survey table...
Could someone please help me with this task...as this is a very urgent task
August 30, 2010 at 3:54 am
Please read and follow the advice given in the first link in my signature and provide ready to use sample data. Furthermore, please include your expected result based on the sample and what you've tried so far.
At a first glance it seems like a task for CrossTab or even DynamicCrossTab. You can find an explanation of both in the related links in my signature. If you need further assitance, please provide ready to use sample data.
As a side note: if it's an urgent issue you might want to consider getting a consultant in, since you shouldn't rely on a forum response time.
August 30, 2010 at 11:38 am
Lucky9 (8/29/2010)
I work for a small client and which has around 1000 customers. For each customer we do a survey and we ask questions and customers give answers for each question.we have like 50 questions for each customer and depends on the customer purchases...we might ask only 20 out of 50 questions for first customer and for second customer we might ask only 40 questions out of 50 questions and for third customer we might ask all the 50 questions....
My requirement is to display the columns from the above tables in just one row.....
Hi.....I am struggling to understand why you require all this on one row...is this for some form of further analysis ?
anyway...here's a suggestion for you to play around with...based loosely on the example you gave....
I have no idea of its perfomance or scalability on larger data sets.
I am sure though, that if you put some effort into providing the neccesary scripts to create the tables and data then some other memebers on this forum will happily add their own thoughts and far better ideas than the one I propose...
Good luck :Whistling:
Kind regards Graham
USE [tempDB]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customer]') AND type in (N'U'))
DROP TABLE [dbo].[Customer]
GO
CREATE TABLE [dbo].[Customer](
[CustId] [int] NOT NULL,
[CustName] [char](50) NULL,
[CustAddress] [char](50) NULL,
[CustContact] [char](50) NULL
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustSurvey]') AND type in (N'U'))
DROP TABLE [dbo].[CustSurvey]
GO
CREATE TABLE [dbo].[CustSurvey](
[CustID] [int] NOT NULL,
[SurveyID] [int] NOT NULL,
[QuestionID] [int] NULL,
[AnswerID] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (1, 1, 1, 3)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (1, 2, 5, 4)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (2, 3, 4, 2)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (2, 4, 7, 9)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (2, 5, 2, 5)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (3, 1, 1, 2)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (3, 6, 2, 12)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (3, 7, 5, 17)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (3, 8, 3, 2)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (3, 9, 1, 3)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (3, 1, 2, 4)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (3, 1, 3, 5)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (3, 1, 4, 1)
INSERT [dbo].[CustSurvey] ([CustID], [SurveyID], [QuestionID], [AnswerID]) VALUES (3, 1, 5, 4)
INSERT [dbo].[Customer] ([CustId], [CustName], [CustAddress], [CustContact]) VALUES (1, 'Jones', 'Home', 'tel 123456')
INSERT [dbo].[Customer] ([CustId], [CustName], [CustAddress], [CustContact]) VALUES (2, 'Smith', 'Away', 'mob 987654')
INSERT [dbo].[Customer] ([CustId], [CustName], [CustAddress], [CustContact]) VALUES (3, 'Wilson', 'Wherever', 'fax 554433')
GO
;With Results (CustId, CustName, CustAddress,CustContact,SurveyID, QuestionID, AnswerID)
AS
(SELECT C.CustId, C.CustName, C.CustAddress, C.CustContact, CS.SurveyID, CS.QuestionID, CS.AnswerID
FROM dbo.Customer C
INNER JOIN dbo.CustSurvey CS ON C.CustId = CS.CustID )
SELECT
Custid,
CustName,
CustAddress,
CustContact,
STUFF(
(
SELECT ' | ' + 'SurveyID ' + Cast(SurveyId as varchar)
+ ' QId' + Cast(QuestionId as varchar)
+ ' QAns' + Cast(AnswerId as varchar)
FROM Results r2
WHERE r1.Custid = r2.Custid
ORDER BY r2.SurveyId, r2.QuestionID --- sort
FOR XML PATH('')),1,1,' ') as SurveyResults
FROM Results r1
GROUP BY Custid, CustName,CustAddress, CustContact
Edited typo in code
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply