rows to columns

25 pts.
Tags:
SQL
SQL Server
I have a table containing answers from different forms. the forms are created dynamically so some forms will have 3 answer fields, some will have as much as 20 answerfields. ex : form_id field_id unique_id field_value 1000 631 1 john doe 1000 632 2 washington 1000 633 3 john@ws.com 1000 631 4 anny doe 1000 632 5 san diego 1000 ...... .... ............ now i want a recordset containing something like this john doe washington john@ws.com anny doe san diego ...... so the rows have to become columns..... any help would be appreciated

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

What you’re trying to do, displaying several items from different rows as several columns in a single row, is called a “pivot”. The basic technique is to use CASE (or something else with if-then-else logic) to pick the value for just one field, and use GROUP BY to combine all the fields for a record, as shown below.

I assume you have a column (I’ll call it record_id) that tells which record each row belongs in. That is, the rows with unique_ids 1 and 2 will have the same record_id if the name on row 1 goes with the city on row 2. (If you don’t have such a column, how do you know that John is from Washington? He could be from San Diego, or his city could be missing.) This solution assumes the combination of record_id and field_id is unique: there doesn’t have to be a field_id=631 for record_id=101, but there can’t be more than one (or else some data will not be displayed).

<pre>
SELECT record_id
, MIN (CASE field_id WHEN 631 THEN field_value END) AS name
, MIN (CASE field_id WHEN 632 THEN field_value END) AS city
, MIN (CASE field_id WHEN 633 THEN field_value END) AS e_mail
FROM table_x
GROUP BY record_id
ORDER BY record_id;
</pre>

In addition to this technique you can also use the PIVOT command is you are using SQL Server 2005 or above.

SELECT form_id, [631] as [name], [632] as [city], [633] as [e_mail]
FROM (SELECT form_id, field_id, field_value
FROM table) as SourceTable
PIVOT
(MAX(field_value)
FOR field_id IN ([631], [632], [633])
) as PivotTable;

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,290 pointsBadges:
    report
  • Aabbccddee
    Thanks, that's exactly what i was trying to do and it works fine ! except if the statement gets to long.... I create the statement on the fly, meaning I create a stored procedure with a local variable @sql in which I concatenate the whole statement. ex : @sql = 'SELECT record_id, ' then the rest of the statement is built within a cursor loop becasue i don't know beforehand how many answerfields the form has @sql = @sql + 'MIN (CASE field_id WHEN 631 THEN field_value END), ....' end of the loop @sql = @sql + 'FROM table_x GROUP BY record_id ORDER BY record_id' and then this statement is executed using sp_executesql @sql and then the problems occur depending on the length of the statement..... because you cannot declare a local variable as a text or ntext data type, the length of the statement cannot go over a datatype nvarchar(4000) or that is the explanation I found in the books online. So does anybody have a suggestion how to solve this?
    25 pointsBadges:
    report
  • Denny Cherry
    Unfortunately there is no way to run a dynamic SQL command using sp_executesql which is longer than 4000 characters. If you don't need to pass in variables you can use the EXECUTE command which will accept commands using data types of VARCHAR or VARCHAR(MAX) so you can send in much larger commands.
    66,290 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following