SQL Tabular Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Tabular Query

Author

Topic

thinkinggeeks
Starting Member

2 Posts

Posted - 2007-10-21 : 00:14:36

Hi
I will give an example to present my problem.

I have a table for formresults.
----------------------------------------------------
|id |record_id |form_field_id|Value |
----------------------------------------------------
|1 |1 |<x> |Lahiru
|2 |1 |<y> |NYC, USA
|3 |2 |<x> |Roshan
|4 |2 |<y> |Panadura, SL
-----------------------------------------------------

record_id are the values for a purticular record. I need a table based on form_field id as the column name and record id as the row.

E.g.

------------------------------------------
|<x> |<y> |
-------------------------------------------
|Lahiru |NYC, USA |
|Roshan |Panadura, Sri Lanka |
-------------------------------------------

How can I do this in SQL? IS this Possible?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-21 : 03:04:01

[code]DECLARE @sample TABLE
(
id int,
record_id int,
form_field_id varchar(4),
Value varchar(20)
)
INSERT INTO @sample
SELECT 1, 1, '<x>', 'Lahiru' UNION ALL
SELECT 2, 1, '<y>', 'NYC, USA' UNION ALL
SELECT 3, 2, '<x>', 'Roshan' UNION ALL
SELECT 4, 2, '<y>', 'Panadura, SL'

-- using SQL 2005's PIVOT
SELECT [<x>], [<y>]
FROM
(
SELECT record_id, form_field_id, Value
FROM @sample
) p
PIVOT
(
MAX (Value)
FOR form_field_id IN
(
[<x>], [<y>]
)
) AS pvt
ORDER BY record_id

-- SQL 2000 style
SELECT [<x>] = MAX(CASE WHEN form_field_id = '<x>' THEN Value END),
[<y>] = MAX(CASE WHEN form_field_id = '<y>' THEN Value END)
FROM @sample
GROUP BY record_id[/code]

KH
[spoiler]Time is always against us[/spoiler]

thinkinggeeks
Starting Member

2 Posts

Posted - 2007-10-21 : 10:37:56

hi,

thanks for the reply. actually i dont understand the pivot concept. anyway i ll try to get some knowledge about it.

But theres one problem. The list of column types (x , y) are not static. they are also read from a table.

Anyway thank you very much for the support!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources