Any one can help me please with this issue? I really appreciate this help. The number of Answer (Q1) is dynamic. we never know exactly number.
When is homework due?
One thing that comes to mind
Is
Dynamic .. pivot
Transpose column to rows
Please Google search
Lots of articles
Obviously if you have enough experience
It's easy to understand the article
Find one that suits and do
I am very busy
Don't have time..
Some experts on this forum can help you
Scott pletcher to the rescue. . thanks Scott
Another thought is
Join makes data come like that
How to join ...to get data in a row is the question
Dynamically
Perhaps dynamic SQL
A bit dirty but hopefully should work for you:
NOTE: returning the last Q4 as well which does not have any answers (you can remove this as well, hoping you will figure this out)
DECLARE @Q TABLE(ID INT IDENTITY(1,1), Question VARCHAR(50))
INSERT INTO @Q(Question)VALUES('Q1'),('Q2'),('Q3'),('Q4')
DECLARE @A TABLE(Ident INT IDENTITY(1,1),ID INT, Answer VARCHAR(50))
INSERT INTO @A(ID,Answer)VALUES(1,'A'),(1,'B'),(1,'C'),(2,'A'),(2,'B'),(2,'C'),(2,'OPTION'),(3,'E'),(3,'F')
--SELECT * FROM @Q
--SELECT * FROM @A
IF OBJECT_ID('TEMPDB..#TMP') IS NOT NULL
DROP TABLE #TMP
IF OBJECT_ID('TEMPDB..#T') IS NOT NULL
DROP TABLE #T
IF OBJECT_ID('TEMPDB..#TMP_A') IS NOT NULL
DROP TABLE #TMP_A
CREATE TABLE #TMP(ID INT , Question VARCHAR(50))
INSERT INTO #TMP(ID, Question) SELECT ID,Question FROM @Q
--SELECT * FROM #TMP
SELECT DISTINCT IDENTITY(INT,1,1) ID, Answer INTO #T FROM @A
--SELECT * FROM #T
DECLARE @SQL VARCHAR(MAX)
--SELECT DISTINCT @SQL=CONCAT('ALTER TABLE #TMP ADD Anwer_',Answer, ' VARCHAR(50)') FROM @A
SET @SQL = STUFF(
(SELECT DISTINCT CONCAT(',Answer_',ID,' VARCHAR(50)') FROM #T FOR XML PATH('')
),1,1,'')
SELECT @SQL=CONCAT('ALTER TABLE #TMP ADD ',@SQL)
--SELECT @SQL
EXEC(@SQL)
SELECT *,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS ROWNUM
INTO #TMP_A
FROM @A
SET @SQL = STUFF(
(
SELECT CONCAT(';','UPDATE T SET Answer_',a.RowNum,'=''',a.Answer, ''' FROM #TMP T INNER JOIN #TMP_A a ON T.ID=A.ID WHERE T.ID=',T.ID)
FROM #TMP T INNER JOIN #TMP_A a ON T.ID=A.ID
FOR XML PATH('')
),1,1,'')
--SELECT @SQL
EXEC(@SQL)
SELECT * FROM #TMP
Hi rocknpop,
many thanks, I will test the solution and let you know.
hi
i tried to do this
hope it helps 
please click arrow to the left for drop create data
drop table #Question
go
create table #Question
(
ID int ,
Question varchar(10)
)
go
insert into #Question select 1,'Q1'
insert into #Question select 2,'Q2'
insert into #Question select 3,'Q3'
insert into #Question select 4,'Q4'
go
--select * from #Question
--go
drop table #Answer
create table #Answer
(
ID int ,
Answer varchar(10)
)
go
insert into #Answer select 1,'A'
insert into #Answer select 1,'B'
insert into #Answer select 1,'C'
insert into #Answer select 1,'OPTION'
insert into #Answer select 2,'A'
insert into #Answer select 2,'B'
insert into #Answer select 2,'C'
insert into #Answer select 3,'E'
insert into #Answer select 3,'F'
go
--select * from #Answer
--go
i used dynamic pivot
please click arrow to the left for SQL
DECLARE @columns NVARCHAR(max),
@sql NVARCHAR(max);
SET @columns = N'';
SELECT @columns += N', p.' + Quotename(answer)
FROM (SELECT DISTINCT p.answer
FROM #answer AS p) AS x;
PRINT @columns
SET @sql = N' SELECT id,' + Stuff(@columns, 1, 2, '')
+ ' FROM ( select q.id,q.Question,a.answer from #Question q join #Answer a on q.id = a.id ) AS j PIVOT ( MAX(Answer) FOR Answer IN ('
+ Stuff(Replace(@columns, ', p.[', ',['), 1, 1, '')
+ ') ) AS p;';
PRINT @sql;
EXEC Sp_executesql
@sql;
