How to display the rows as column.....

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

Old Forums

CLOSED - General SQL Server

How to display the rows as column.....

Author

Topic

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-05-20 : 03:05:59

Hi All
Is it possible to return the rows as column... i think am confusing.. for example

i have a table with 2 columns

col1 col2
=======================
101 a
102 a
103 a
104 b
105 b

i want to selct the col1 values if col2 value is 'a'

select col1 from tbl where col2='a'

it ll return o/p like this

col1
===========
101
102
103

but i dont want my o/p in this format

i want display the result like this

col1 col1 col1
================================
101 102 103

is this possible...

thats wat i told 'i want to display the result rows as columns'

can anyone explain me how to do this.......

Live while u r Alive

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-20 : 04:20:11

Where do you want to show the data?
Use reports and its cross tab features
Refer this an example




Declare @t table(num int)
insert into @t 
	select 101 union all 
	select 102 union all 
	select 103 
Select num from @t
Declare @s varchar(8000)
Select @s=COALESCE(@s+',','')+cast(num as varchar(10)) from @t
Exec('Select '+@s)

Also refer
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-05-20 : 04:37:47

I want to get these result in Dataset and convert it to an XML file.. and i need to display all the columns with the same name...

my XML format is like this

<test>
<date>10-02-2006</date>
<time>11.00 AM</time>
<tranCount>4</tranCount>
<tranDetail>
<tranID>001</tranID>
<tranID>002</tranID>
<tranID>003</tranID>
<tranID>004</tranID>
</tranDetail>
</test>

i wrote that sample query for the <tranDetail> table

i have a problem converting the dataset to XML,becoz it displays the result like this

<test>
<date>10-02-2006</date>
<time>11.00 AM</time>
<tranCount>4</tranCount>
<tranDetail>
<tranID>001</tranID>
</tranDetail>
</test>
<tranDetail>
<tranID>002</tranID>
</tranDetail>
<tranDetail>
<tranID>003</tranID>
</tranDetail>
<tranDetail>
<tranID>001</tranID>
</tranDetail>

this is because my SP returns the result as rows and my Dataset convert it as seperate table..
so avoid this conflict i have to change my query to and get the results in a column format..

But one thing every column shoul contain the same name like tranID

Thanks

Ram

Live while u r Alive

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-05-20 : 05:08:15

Thanks madhi
ur query working fine.. But it returns the results without column name

This is ur query result

(3 row(s) affected)

num
-----------
101
102
103

(3 row(s) affected)

----------- ----------- -----------
101 102 103

but
i need to show the column name(same).. so that XML file will be in the corrct format

i try to give the column name by using 'as' alias but it throws an error.. where do i have to give so that it ll work

tranID tranID tranID
----------- ----------- ----------- this is the expected format
101 102 103

Thanks

=====================
Ram

Live while u r Alive

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-20 : 05:43:19

Modified from Madhivanan's code

declare @t table(num int)
insert into @t 
	select 101 union all 
	select 102 union all 
	select 103 
select num from @t
declare @s varchar(8000)
select 	@s = COALESCE(@s + ',', '') + cast(num as varchar(10)) + ' as tranID'
from 	@t
exec('select '+@s)

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-05-20 : 06:53:35

Thanks Tan
i got the expected results... now i am able to generate the structured XML..

Thank u guys..

=====================
Ram

Live while u r Alive

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-20 : 07:45:49

Tan, your suggestion gives the column name TranId for all the columns. You cant retrieve the value by specifying column name in recordset in front end application. Rs("Tranid") will lead to error as there are more rows with the same column name. Instead make them unique


declare @t table(num int)
insert into @t 
	select 101 union all 
	select 102 union all 
	select 103 
select num from @t
declare @s varchar(8000)
select 	@s = COALESCE(@s + ',', '') + cast(num as varchar(10)) + 'col'+cast(num as varchar(10))
from 	@t
exec('select '+@s)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-20 : 09:28:12

Actually this is my initial post

declare @t table(num int)
insert into @t 
	select 101 union all 
	select 102 union all 
	select 103 
select num from @t
declare @s varchar(8000)
declare @n int
select  @n = 1
select 	@s = COALESCE(@s + ',', '') + cast(num as varchar(10)) + 'TranID'+cast(@n as varchar(10)),
	@n = @n + 1
from 	@t
exec('select '+@s)

but later noticed the expected result as

quote:

tranID      tranID      tranID
----------- ----------- ----------- this is the expected format
101         102         103

And hence make the changes to same column name.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-22 : 08:11:27

Hi all,

Ram - Assuming I understand you correctly, I can't help but get the feeling that the approach you're using to your (overall) problem isn't the best. Take a look at the code below as an example alternative approach - it should jump straight to the XML you're after...

--data
set dateformat mdy
declare @tran table (id int, dtm datetime)
insert @tran
          select 1, '2006-10-02 11:00'

declare @tranDetail table (tranid int, col1 int, col2 varchar(10))
insert @tranDetail
          select 1, 101, 'a'
union all select 1, 102, 'a' 
union all select 1, 103, 'a'
union all select 1, 104, 'b'
union all select 1, 105, 'b'

--calculation
select
    1 as tag,
    null as parent,
    convert(varchar(10), dtm, 110) as 'test!1!date!element',
    stuff(right(convert(varchar(20), dtm, 100), 7), 6, 0, ' ') as 'test!1!time!element',
    (select count(*) from @tranDetail where col2 = 'a') as 'test!1!tranCount!element',
    null as 'tranDetail!2',
    null as 'tranID!3'
from @tran
union all
select 2 as tag, 1 as parent, null, null, null, null, null
union all
select 3, 2, null, null, null, null, col1 from @tranDetail where col2 = 'a'

for xml explicit

--results
---------------------------------
<test>
	<date>10-02-2006</date>
	<time>11:00:00</time>
	<tranCount>3</tranCount>
	<tranDetail>
		<tranID>101</tranID>
		<tranID>102</tranID>
		<tranID>103</tranID>
	</tranDetail>
</test>

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-05-22 : 08:47:37

Ryan
This is great stuff.... Can u xplain me how this query works.. it may be useful for many guys who r seeking the solution to XML problem

I really appriciate this....

=====================
Ram

Live while u r Alive

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-22 : 08:53:48

Thanks Ram - glad you like it

Take a look at 'EXPLICIT mode' in BOL - there's some detailed explanation of this kind of stuff in there. It's quite tricky to get your head around at first, but can be used to create any shape of XML document when you do

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-23 : 01:56:17

Good Stuff Ryan

Madhivanan

Failing to plan is Planning to fail

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-05-25 : 04:42:39

Hi all
these links a r useful if u realy want to know about SQLXML as a beginner

http://www.quest-pipelines.com/newsletter-v3/0502_B.htm
http://www.15seconds.com/issue/001102.htm

This link gives the great amount of details for .NET developers to integrate XML solutions

http://www.topxml.com/sqlxml/raw_mode.asp

=====================
Ram

Live while u r Alive

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-25 : 05:09:09

Thanks for the links

Madhivanan

Failing to plan is Planning to fail

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-05-25 : 06:52:46

Hi all
I have faced another one problem while executin this SQLXML query.My Xml output is a large file.It nearly contains more than 500 tranIDs. but while executing this script only within 20 TranIDs are listed.Remainin are truncated.

is there any restriction in string to hold the length of data (any restiction in size) ,becaus XML query returns the XMLoutput as string. half of the XML is getting truncated..

Anyone know the solution for this...

=====================
Ram

Live while u r Alive

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-25 : 09:54:02

Ram - Where are you running the SQL from? If it's from Query Analyzer you won't be able to see more than 8000 characters. You'll need to do it from VB, say, as in the 15seconds article link you posted. That shouldn't have any size restrictions.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-05-26 : 00:39:53

Thanks Ryan.
I already got it.I was mistakenly post that query before accessin it through Frontend.. Now things are fine.. thanks for ur reply

=====================
Ram

Live while u r Alive

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-09-15 : 06:34:55

Hi All,
I need help regarding this Open XML method.. i want to store the returned XML into the variable or temp table or table variable.. i tried this but i cant insert into an table variable.. it throws an error like this
The FOR XML clause is not allowed in a INSERT statement... i dont want to return this back to front end.. my actual requirement is to pass this XML to another one SP as inputis this possible guyz...

How can i do this....

=====================
Ram
think BIG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources