How can I reshape a table in SQL Server?

Question

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

At the URL you provided there is an answer by bluefeet that gives the "dynamic" version of a "pivot" query, it's a little disappointing you weren't able to interpret that as being suited to your needs, it warrants an upvote. — Paul Maxwell, Nov 05 '17 at 06:05

Paul Maxwell · Accepted Answer · 2017-11-06T06:26:56.320

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.

when I run this query I got this error : An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name — Maryam, Nov 06 '17 at 06:03
The example query I show above is tested (as you can see there are results). These recent error arise because you have amended the query (*for an into something*) AND your column names are derived from your data. I'm afraid the recent errors are your responsibility. One thing you can do is to NOT execute the `@query` but simply select it instead. (see the second last line of my example) THEN paste the generated script into a sql formatter eg http://sql-format.com — Paul Maxwell, Nov 06 '17 at 06:10
oh **"verify each column has a name"** this may indicate that you are picking-up a NULL (or empty field '' ) when `@cols` is being created, so the first half of the query is probably when that happens. stop the query after `@cols` is established and select in (where I have -- select `@cols`) and look through that result. each column MUST have a name — Paul Maxwell, Nov 06 '17 at 06:19

How can I reshape a table in SQL Server?

1 Answers1

Debugging advice