formatting the output in tables

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

SQL Server 2012 Forums

Transact-SQL (2012)

formatting the output in tables

Author

Topic

misterraj
Yak Posting Veteran

94 Posts

Posted - 2014-12-14 : 23:56:47

I have a table where I want the output to be a string. ie.,

table1: (containing desc1 desc2 and category as columns)

desc1__________ desc2__________________ category

bag ____________fancybag for children __children
wallet__________ wallets for women______ women
scarf____________tutuya scarf____________children

i want the output returned as text.
<category=children>
<h1>bag<h1>
<div> fancybag for children </div>
<h1>scarf<h1>
<div> tutuya scarf </div>

<category=women>
<h1>wallet<h1>
<div> wallets for women </div>

is there a way to get the output. Kindly help. Is it possible to use a select statement and get this done?

thanks
Venkat.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-15 : 01:05:12

if it is as simple as that, then:

[code]
select '<h1>' + desc1 + '</h1>' + char(10) + char(13)
+ '<div>' + desc2 + '</div>' + char(10) + char(13)
from mytable

misterraj
Yak Posting Veteran

94 Posts

Posted - 2014-12-15 : 04:13:03

the catch here is, category should be grouped. Is that still possible?
Also can I get the result of these queries in a string variable in Stored proc? Please help.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-15 : 17:08:22

WHy didn't you state all your requirements in the first place?

Yes, it's possible to group by category. show some sample output.

also you can always write

declare @str as varchar(max)
select @str = ...

misterraj
Yak Posting Veteran

94 Posts

Posted - 2014-12-18 : 12:28:58

gbritton,
this is what it is:
sample input and output as desired:

desc1__________ desc2__________________ category

bag ____________fancybag for children __children
wallet__________ wallets for women______ women
scarf____________tutuya scarf____________children

i want the output returned as text.
<category=children>
<h1>bag<h1>
<div> fancybag for children </div>
<h1>scarf<h1>
<div> tutuya scarf </div>

<category=women>
<h1>wallet<h1>
<div> wallets for women </div>

is there a way to get the output. Kindly help. Is it possible to use a select statement and get this done?

thanks
Venkat.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 13:19:39

I solved it this way:


declare @t table(desc1 varchar(50), desc2 varchar(50), category varchar(50))

insert into @t(desc1, desc2, category) values

('bag' ,'fancybag for children' ,'children'),
('wallet', 'wallets for women', 'women'),
('scarf','tutuya scarf','children')

select case rn
		   when 1 then 
			    '<category=' + category + '>' + char(10)
			  + '<h1>' + desc1 + '</h1>' + char(10)
			  + '<div>' + desc2 + '</div>'
		   else '<h1>' + desc1 + '</h1>' + char(10)
	              + '<div>' + desc2 + '</div>' 
	   end
from
(
	select rn = ROW_NUMBER() over(partition by category order by desc1, desc2)
		 , category
		 , desc1
		 , desc2
	from @t t
) t

order by category, desc1, desc2

misterraj
Yak Posting Veteran

94 Posts

Posted - 2014-12-19 : 10:42:33

o. thanks,

what is rn? in select case statement?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-19 : 11:05:53

from:

rn = ROW_NUMBER() over(partition by category order by desc1, desc2)

in my solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources