CODEWITHSUNDEEP
t-sqlpivot
Maryam
Maryam

Reputation: 21

How can I reshape a table in SQL Server?

We have three tables: users, answer and question.

Let's suppose that our result from joining these tables would be like that :

userid    firstname ordLastName question                        answer
---------------------------------------------------------------------------
8591        Larry   Marshburn   Type of Surgery:                Colostomy  
8591        Larry   Marshburn   Month of Surgery:               2
8591        Larry   Marshburn   Year of surgery:                2010
8591        Larry   Marshburn   Current Ostomy System Brand:    ConvaTec  
8593        Melvin  Belcher     Type of Surgery:                Urostomy
8593        Melvin  Belcher     Month of Surgery:               9
8593        Melvin  Belcher     Year of surgery:                2010
8593        Melvin  Belcher     Current Ostomy System Brand:    ConvaTec

but our desired result looks like this: 

userid      name  "Type of Surgery" "Month of Surgery" "Year of Surgery" etc.
8591        Larry   Marshbourn   Colostomy         2                  2010
8593        Melvin  Belcher      Urostomy          9                  2010

I searched Stackoverflow site and I found this answer : [Poor Man's SQL Pivot. List Questions as Columns and Answers per User in one row

which it is correct but the problem is that we have a lot of question and answers that we can't list them like q1,q2, .....q1000

Appreciate if anyone can help

Upvotes: 0

Views: 874

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35593

Natively SQL does NOT like column names that "change", so when you decide that the columns have to be based on some set of questions, you will needs what is known as "dynamic sql", which is SQL that generates other SQL. Then that generated SQL gets executed.

There are "issues" to consider. For example there is no specific length restriction to a question, but a column names cannot exceed 128 characters. In the code below I have used an arbitrary limit of 60 [using a LEFT(question,60)] which you can tinker with to suit.

The other issue is you chose to share just a result of 3 tables, so you will need to insert your own logic into the code where you find users_answers_questions which include how you create the @cols variable which really is more efficiently created from a questions table, not an answers table.

You can play with this at SQL Fiddle nb: code borrowed/tweaked from here (by bluefeet)

CREATE TABLE users_answers_questions
    ([userid] int, [firstname] varchar(6), [LastName] varchar(9), [question] varchar(28), [answer] varchar(9))
;

INSERT INTO users_answers_questions
    ([userid], [firstname], [LastName], [question], [answer])
VALUES
    (8591, 'Larry', 'Marshburn', 'Type of Surgery:', 'Colostomy'),
    (8591, 'Larry', 'Marshburn', 'Month of Surgery:', '2'),
    (8591, 'Larry', 'Marshburn', 'Year of surgery:', '2010'),
    (8591, 'Larry', 'Marshburn', 'Current Ostomy System Brand:', 'ConvaTec'),
    (8593, 'Melvin', 'Belcher', 'Type of Surgery:', 'Urostomy'),
    (8593, 'Melvin', 'Belcher', 'Month of Surgery:', '9'),
    (8593, 'Melvin', 'Belcher', 'Year of surgery:', '2010'),
    (8593, 'Melvin', 'Belcher', 'Current Ostomy System Brand:', 'ConvaTec')
;

Query 1:

DECLARE @cols AS nvarchar(max)
      , @query AS nvarchar(max)

SELECT
      @cols = STUFF((
            SELECT DISTINCT
                  ', ' + QUOTENAME(left(question,60))
            FROM users_answers_questions
            FOR xml PATH (''), TYPE
      )
      .value('.', 'NVARCHAR(MAX)')
      , 1, 1, '')

-- select @cols   

SET @query
= 'SELECT userid, firstname, LastName,' + @cols + ' from 
         (
            SELECT userid, firstname, LastName, question, answer
            FROM users_answers_questions
         ) x
         pivot 
         (
            min(answer)
            for question in (' + @cols + ')
         ) p '

--select @query
EXECUTE (@query)

Results:

| userid | firstname |  LastName | Current Ostomy System Brand: | Month of Surgery: | Type of Surgery: | Year of surgery: |
|--------|-----------|-----------|------------------------------|-------------------|------------------|------------------|
|   8591 |     Larry | Marshburn |                     ConvaTec |                 2 |        Colostomy |             2010 |
|   8593 |    Melvin |   Belcher |                     ConvaTec |                 9 |         Urostomy |             2010 |

Debugging advice

When you run "dynamic sql" it is picking-up values from your data which may cause problems. The best (perhaps only) way to assess what is happening is to stop the process and inspect the generated SQL script BEFORE it gets executed.

There are 2 points where this makes sense and BOTH are marked in the query above: -- select @cols and --select @query uncomment one of those and stop at that point. Copy the generated sql into a syntax checker/formatter (there's a nice one for TSQL at http://sql-format.com). If there are problems in that SQL you will need to trace back to where that is being introduced and avoid it it or fix it.

Upvotes: 1

Related Questions