Clever Geek Handbook

Displaying one record (row) as one column

I am looking for ways to display one row of data as one 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?

0


source to share


5 answers


Yes, there is a TSQL, PIVOT team. And there are several existing topics on this topic; but I cannot find one of them.



My usual answer (probably 5 or 6 of these threads) is to think about using Excel or Access if needed - this is a fairly simple way to convey value to end users. But YMMV.

+2


source


You can use unsivot 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 support, but can be fast (need to check) or use dynamic query and union

+2


source


Another easy way:

select "FieldA", "FieldA" as the value from the
join table
select "FieldB", FieldB as the value from the table

but pivot points are what you want to try.

+1


source


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

If you have access to SQL Server Reporting Services, you can also generate a report based on your simple query and use a matrix report. Drag your fields onto the control and you're done!

+1


source


If you are looking for something specific i.e. not a general solution, but one that fits your specific DB, then you can do something like this:

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

Use conversions for nvarchar (255) so that you don't have problems no matter what characters are in it, remember to correct the sort before starting the selection (you never know what will happen to your orderby and groupby otherwise).

-1


source






More articles:


All Articles