Transpose a set of rows as columns in SQL Server 2000

Question!

Is there any facility of transposing rows to columns in SQL Server (it is possible in MS-Access)? I was befuddled because this facility is available in MS-Access but not in SQL Server. Is it by design that this feature has not been included in SQL Server?



Answers

I have Data in following format

Survey_question_ID

Email (User)

Answer

for 1 survey there are 13 question and answers the desired output i wanted was

User ---Survey_question_ID1---Survey_question_ID2

email---answers---answer ........so on

Here is the solution for SQL Server 2000, Cause field data type is TEXT.

DROP TABLE #tmp

DECLARE @tmpTable  TABLE
(
emailno NUMERIC,
question1 VARCHAR(80),
question2 VARCHAR(80),
question3 VARCHAR(80),
question4 VARCHAR(80),
question5 VARCHAR(80),
question6 VARCHAR(80),
question7 VARCHAR(80),
question8 VARCHAR(80),
question9 VARCHAR(80),
question10 VARCHAR(80),
question11 VARCHAR(80),
question12 VARCHAR(80),
question13 VARCHAR(8000)
)

DECLARE @tmpTable2  TABLE
(
emailNumber NUMERIC
)

DECLARE @counter INT
DECLARE @Email INT

SELECT @counter =COUNT(DISTINCT ans.email) FROM answers ans WHERE ans.surveyname=100430 AND ans.qnpkey BETWEEN 233702 AND 233714
SELECT * INTO #tmp FROM @tmpTable
INSERT INTO @tmpTable2 (emailNumber) SELECT DISTINCT CAST(ans.email AS NUMERIC) FROM answers ans WHERE ans.surveyname=100430 AND ans.qnpkey BETWEEN 233702 AND 233714

WHILE @counter >0

BEGIN

        SELECT TOP 1 @Email= emailNumber FROM @tmpTable2
        INSERT INTO @tmpTable (emailno) VALUES (@Email )


        Update @tmpTable set question1=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233702 and [email protected]
        Update @tmpTable set question2=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233703 and [email protected]
        Update @tmpTable set question3=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233704 and [email protected]
        Update @tmpTable set question4=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233705 and [email protected]
        Update @tmpTable set question5=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233706 and [email protected]
        Update @tmpTable set question6=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233707 and [email protected]
        Update @tmpTable set question7=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233708 and [email protected]
        Update @tmpTable set question8=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233709 and [email protected]
        Update @tmpTable set question9=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233710 and [email protected]
        Update @tmpTable set question10=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233711 and [email protected]
        Update @tmpTable set question11=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233712 and [email protected]
        Update @tmpTable set question12=CAST(answer as VARCHAR(80)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233713 and [email protected]
        Update @tmpTable set question13=CAST(answer as VARCHAR(8000)) from answers ans where ans.surveyname=100430 and ans.qnpkey = 233714 and [email protected]

        insert into #tmp select * from  @tmpTable       

        DELETE FROM @tmpTable       
        DELETE FROM @tmpTable2 WHERE emailNumber= @Email

        set @counter [email protected] -1

End

select * from #tmp


Best to limit to small scale for this sort of thing. If you're using SQL 2k though and don't have PIVOT features available, I've drafted a stored proc that should do the job for you. Bit of a botch rush job so pull it apart as much as you like. Paste the below into a sql window and edit the EXEC at the bottom as preferred. If you want to see what's being generated, remove the --s in the middle:

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE XTYPE = 'P' AND NAME = 'USP_LIST_CONCAT')
DROP PROCEDURE USP_LIST_CONCAT
GO

CREATE PROCEDURE USP_LIST_CONCAT (@SourceTable NVARCHAR(1000) = '' ,@SplitColumn NVARCHAR(1000) = '' , @Deli NVARCHAR(10) = '', @KeyColumns NVARCHAR(2000) = '' , @Condition NVARCHAR(1000) = '')
AS
BEGIN
SET NOCOUNT ON

/* PROCEDURE CREATED 2010 FOR SQL SERVER 2000. SIMON HUGHES. */
/* NOTES: REMOVE --'s BELOW TO LIST GENERATED SQL. */

IF @SourceTable = '' OR @SourceTable = '?' OR @SourceTable = '/?' OR @SplitColumn = '' OR @KeyColumns = ''
BEGIN
PRINT 'Format for use:'
PRINT ' USP_LIST_CONCAT ''SourceTable'', ''SplitColumn'', ''Deli'', ''KeyColumn1,...'', ''Column1 = 12345 AND ...'''
PRINT ''
PRINT 'Description:'
PRINT 'The SourceTable should contain a number of records acting as a list of values.'
PRINT 'The SplitColumn should be the name of the column holding the values wanted.'
PRINT 'The Delimiter may be any single character or string ie ''/'''
PRINT 'The KeyColumn may contain a comma separated list of columns that will be returned before the concatenated list.'
PRINT 'The optional Conditions may be left blank or may include the following as examples:'
PRINT ' ''Column1 = 12334 AND (Column2 = ''ABC'' OR Column3 = ''DEF'')'''
PRINT ''
PRINT 'A standard list in the format:'
PRINT ' Store1, Employee1, Rabbits'
PRINT ' Store1, Employee1, Dogs'
PRINT ' Store1, Employee1, Cats'
PRINT ' Store1, Employee2, Dogs'
PRINT ''
PRINT 'Will be returned as:'
PRINT ' Store1, Employee1, Cats/Dogs/Rabbits'
PRINT ' Store1, Employee2, Dogs'
PRINT ''
PRINT 'A full ORDER BY and DISTINCT is included'
RETURN -1
END


DECLARE @SQLStatement NVARCHAR(4000)

SELECT @SQLStatement = '
DECLARE @DynamicSQLStatement NVARCHAR(4000)

SELECT @DynamicSQLStatement = ''SELECT '[email protected]+', SUBSTRING(''

SELECT @DynamicSQLStatement = @DynamicSQLStatement + '' + '' + CHAR(10) +
'' MAX(CASE WHEN '[email protected]+' = ''''''+RTRIM('[email protected]+')+'''''' THEN '''''[email protected]+'''+RTRIM('[email protected]+')+'''''' ELSE '''''''' END)''
FROM '+ @SourceTable +' ORDER BY '[email protected]+'

SELECT @DynamicSQLStatement = @DynamicSQLStatement + '' ,2,7999) List'' + CHAR(10) + ''FROM '+ @SourceTable+''' + CHAR(10) +'''+CASE WHEN @Condition = '' THEN '/* WHERE */' ELSE 'WHERE '[email protected] END+ '''+ CHAR(10) + ''GROUP BY '[email protected]+'''

SELECT @DynamicSQLStatement = REPLACE(@DynamicSQLStatement,''( +'',''('')

-- SELECT @DynamicSQLStatement -- DEBUG ONLY
EXEC (@DynamicSQLStatement)'

EXEC (@SQLStatement)

END
GO

EXEC USP_LIST_CONCAT 'MyTableName', 'ColumnForListing', 'Delimiter', 'KeyCol1, KeyCol2', 'Column1 = 123456'
By : SimonH_UK


This video can help you solving your question :)
By: admin