I've been trying to figure out how to solve this for a while, with no success. I have 2 tables:
1)QUESTIONS
ID (pk)
QUES
ORD
2)ANSWERS
ID (pk)
QUES_ID (fk)
SURV_ID (fk)
ANSW
Now I need to create a view, where the questions are the columns names and for each SURV_ID there is a row in the view with answer NULL if there is no entry in ANSWERS or answer ANSW if there is an entry. Not sure if it is possible to do it with JOINs.
I was thinking to use a CURSOR, then do the UNION of the resulting tables, then use a PIVOT, but not sure it's the best solutions + I don't know how to do the union through the loops...
DECLARE @survID int
DECLARE db_cursor CURSOR FOR
SELECT DISTINCT SURV_ID FROM ANSWERS ORDER BY SURV_ID
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @survID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT QUES
,ANSW , SURV_ID
FROM QUESTIONS a
left outer JOIN ANSWERS b ON a.ID = b.QUES_ID
WHERE ( SURV_ID = @survID OR SURV_ID IS NULL)
FETCH NEXT FROM db_cursor INTO @survID
END
CLOSE db_cursor
DEALLOCATE db_cursor
....
UPDATE: The problem that I am facing is a bit more complicated, since not all the questions have an answer (in that case I'm happy to show NULL), and answers are grouped by a SURV_ID
CREATE TABLE [QUESTIONS] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[QUES] [varchar](250) NOT NULL,
[ORD] [int] NOT NULL,
CONSTRAINT [PK_QUESTIONS] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [QUESTIONS] values ('First Question', 1)
INSERT INTO [QUESTIONS] values ('Second Question', 2)
GO
CREATE TABLE [ANSWERS] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[SURV_ID] [int] NOT NULL,
[QUES_ID] [int] NOT NULL,
[ANSW] [varchar](500) NOT NULL,
CONSTRAINT [PK_ANSWERS] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [ANSWERS] WITH CHECK ADD CONSTRAINT [FK_ANSWERS_QUESTIONS] FOREIGN KEY([QUES_ID])
REFERENCES [QUESTIONS] ([ID])
GO
ALTER TABLE [ANSWERS] CHECK CONSTRAINT [FK_ANSWERS_QUESTIONS]
GO
INSERT INTO [ANSWERS] ([SURV_ID],[QUES_ID],[ANSW]) VALUES (1,1,'Y')
INSERT INTO [ANSWERS] ([SURV_ID],[QUES_ID],[ANSW]) VALUES (1,2,'N')
INSERT INTO [ANSWERS] ([SURV_ID],[QUES_ID],[ANSW]) VALUES (2,1,'N')
INSERT INTO [ANSWERS] ([SURV_ID],[QUES_ID],[ANSW]) VALUES (3,2,'Y')
INSERT INTO [ANSWERS] ([SURV_ID],[QUES_ID],[ANSW]) VALUES (4,1,'Y')
INSERT INTO [ANSWERS] ([SURV_ID],[QUES_ID],[ANSW]) VALUES (4,2,'Y')
GO
What I would like to get, is something like
[SURV_ID] [First Question] [Second Question]
1 Y N
2 N null
3 null Y
4 Y Y
It looks like you are working in SQL-Server, so the following should work. If you know the number of items that you want to transform, then you can hard-code the values:
select *
from
(
select a.surv_id,
a.answ,
q.ques
from QUESTIONS q
left join ANSWERS a
on q.ID = a.QUES_ID
) x
pivot
(
max(answ)
for ques in ([First Question], [Second Question])
) p
If you do not know the number of columns, then you can use dynamic sql:
DECLARE @query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsPivot = STUFF((SELECT distinct ','
+ quotename(ques)
from QUESTIONS
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select *
from
(
select a.surv_id,
a.answ,
q.ques
from QUESTIONS q
left join ANSWERS a
on q.ID = a.QUES_ID
) x
pivot
(
max(answ)
for ques in ('+ @colspivot +')
) p'
exec(@query)