SQLTeam.com | Weblogs | Forums

Query CTE or other Windows function

question

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 :slight_smile: :slight_smile:

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;

sqlteam