Display a single record (row) as a single column

Question

I'm looking for ways to display a single row of data as a single column (with multiple rows). For example,

FieldA  FieldB
------- ---------
1       Some Text [row]


Header Value [col]
------ ------
FieldA 1          [row1]
FieldB SomeText   [row2]

Is there a way to do this with SQL Server 2005?

Answer 1

Yup, there's a TSQL command, PIVOT. And there are several existing threads on this topic; but I can't find one offhand.

My usual answer (probably 5 or 6 of those threads) is to think about using Excel or Access if appropriate - it's a pretty easy way to deliver value to end-users. But YMMV.

Answer 2

Another simple way:

select 'FieldA', FieldA as Value from table
union
select 'FieldB', FieldB as Value from table

but pivots are really what you want to try for.

Answer 3

Like several other responses have suggested, use the T-SQL PIVOT command.

If you have access to SQL Server Reporting Services, You could alternatively create a report based on your simple query and use the matrix report control. Drag and drop your fields onto the control, and you are done!

Answer 4

You can use unpivot explained here

Declare @tbl Table
(
  c1 int,
  c2 int,
  c3 int
)

Insert into @tbl Values(1,2,3)

Select
  cname,
  cval
From
  (
    Select C1,C2,C3 From @tbl
  ) t
UNPIVOT
  (Cval For Cname In
    (C1,C2,C3)
  )As U

But it is usually inflexible and slow. You can use union but it is usually worse in terms of maintaince but might be fast (need to check) or use dynamic query and union

Answer 5

If you're looking for something specific, i.e. not a general solution but one tailored to your specific DB, then you can do something like this:

select Field1+';'+convert(nvarchar(255), Field2 )+';'+convert(nvarchar(255),Field3 ) from Table .

Use conversions to nvarchar(255) so that you won't have problems no matter what chars are in it, remember to fix the collation before you run the select(you never know what's going to happen with your orderby's and groupby's otherwise).

Have fun!

Andrew