Columns & rowns interchage on string the data

[John Jayaseelan]

Hi,

Is it possible to interchange column & row data from the t-sql on
storing into a file?

Thanks


*** Sent via Developersdex http://www.developersdex.com ***

[Simon Hayes]

Re: Columns & rowns interchage on string the data

I'm not sure I understand your question, but I think you're asking if
you can export a crosstab report to a file? If so, a reporting tool is
probably the best general solution (MSSQL Reporting Services, Business
Objects etc), but you could create your own qrosstab queries:

http://www.aspfaq.com/show.asp?id=2462

To export the results to a file, you could use osql.exe, bcp.exe or
DTS.

Simon

[John Jayaseelan]

Re: Columns & rowns interchage on string the data

Simon,

Thanks for your reply. I woule like the following output data

col1 col2 col3 .......

row1 r1c1 r1c2 c1c3 ....

row2 r2c1 r2c2 r2c3 .....

row3 r3c1 r3c2 r3c3 .......

..
..
..


to be output like



row1 row2 row3 ........

col1 r1c1 r2c1 r3c1

col2 r1c2 r2c2 r3c2

col3 r1c3 r2c3 r3c3
..
..
..


Thanks

*** Sent via Developersdex http://www.developersdex.com ***

[David Portas]

Re: Columns & rowns interchage on string the data

Here's an example:

CREATE TABLE foo (foo_key INTEGER PRIMARY KEY, x INTEGER NOT NULL, y
INTEGER NOT NULL, z INTEGER NOT NULL) ;

INSERT INTO foo (foo_key, x, y, z)
SELECT 1,10,11,12 UNION ALL
SELECT 2,20,21,22 UNION ALL
SELECT 3,30,31,32 ;

SELECT foo_key, x, y, z
FROM foo ;

SELECT 'x' AS col,
MAX(CASE WHEN foo_key = 1 THEN x END) AS row1,
MAX(CASE WHEN foo_key = 2 THEN x END) AS row2,
MAX(CASE WHEN foo_key = 3 THEN x END) AS row3
FROM foo
UNION ALL
SELECT 'y',
MAX(CASE WHEN foo_key = 1 THEN y END),
MAX(CASE WHEN foo_key = 2 THEN y END),
MAX(CASE WHEN foo_key = 3 THEN y END)
FROM foo
UNION ALL
SELECT 'z',
MAX(CASE WHEN foo_key = 1 THEN z END),
MAX(CASE WHEN foo_key = 2 THEN z END),
MAX(CASE WHEN foo_key = 3 THEN z END)
FROM foo ;

--
David Portas
SQL Server MVP
--