Hi,
I have a table, and it is returning the data as -
Column1 Column2 Column3 Column4 Column5 Column6
-------------------------------------------------------------------
6 Joy Mycity NZ 123456 [email protected]
I need to disply it as -
SingleColumn
-----------------------
6
joy
mycity
NZ
123456
[email protected]
How do I do it?
Maybe I am missing something but couldn't you do a
SELECT col1 + ' ' + col2 + .... to concatenate them all together.
I'd make the query continue to return the data as you currently are, as individual columns, and instead make the UI code format it as it needs it. Makes it more flexible so you can have x different displays of the same data, all using the one, same query as the data source.
If you "need to display" the data in such a form, I would first retrieve the data--presumably a single row of several columns from one table--from the databases, and then use the tool/language used to retrieve the data (C#, Java, whatever) to format the data as it needs to be displayed by or on whatever is to display it. SQL is designed to store and retrieve data, not to format it for display on a screen.
With that said, if I had to return (say) values from 5 separate columns from one row of one table, I'd hack it something like this:
SELECT Col1 as Data, 1 as MyOrder from MyTable where MyTableId = @DesiredRow
UNION ALL SELECT Col2, 2 from MyTable where MyTableId = @DesiredRow
UNION ALL SELECT Col3, 3from MyTable where MyTableId = @DesiredRow
UNION ALL SELECT Col4, 4 from MyTable where MyTableId = @DesiredRow
UNION ALL SELECT Col5, 5 from MyTable where MyTableId = @DesiredRow
order by MyOrder
(Have to see if that gets properly formatted...) You only have to alias the column in the first select. Hmm, there will be other considerations, such as (1) the data type of the column returned will be the datatype of that first select, so you might need to do a lot of casting, such as
...select cast(Col1 as varchar(100)) As Data...
(2) It will probably order the rows returned in the order they were selected, I tossed in that orderby just to be sure. And, (3) Your mileage may vary, depending on application considerations I don't know about just now.
Good luck!
SQL Server 2005 comes with the PIVOT/UNPIVOT functions which do exactly this.
SELECT SingleColumn FROM
(SELECT CAST(Column1 AS VARCHAR(128)) AS c1,
CAST(Column2 AS VARCHAR(128)) AS c2,
CAST(Column3 AS VARCHAR(128)) AS c3,
CAST(Column4 AS VARCHAR(128)) AS c4,
CAST(Column5 AS VARCHAR(128)) AS c5,
CAST(Column6 AS VARCHAR(128)) AS c6
FROM (...) AS t
) AS p
UNPIVOT (SingleColumn FOR cols IN (c1, c2, c3, c4, c5, c6)) AS unp