how to show Output in single row.

Question

Post reply

how to show Output in single row.

somansam

SSC Veteran

Points: 280
More actions
June 4, 2010 at 7:20 am

#221859

Hi All,

I have table which contain multiple records for one id e.g.

a_id a_add a1_add a3_add a4_dt

1 sameer Mumbai TEST 05/02/2010

1 sameer1 Pune TEST2 08/04/2010

I need to show result in single row like

a_add a1_add a3_add a4_dt(1) a_add a1_add a3_add a4_dt(2)

=====================================================

sameer Mumbai TEST 05/02/2010 sameer1 Pune TEST2 08/04/2010

I have tried PIVOT option but not able succeed.

Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please provide table def, sample data and expected result in a ready to use format as described in the first link in my signature. Also. please provide what you've tried so far and where you got stuck.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 2

Try this; it does the trick but it would work only if you have two or less rows for a particular a_id. I would like to add that this is not an elegant approach but as I said, it does the trick.

if object_id('tempdb..#Multi') is not null drop table #Multi



create table #Multi(a_id smallint, a_add varchar(10), a1_add varchar(10), a3_add varchar(10), a4_dt varchar(10))

insert into #Multi

select 1, 'sameer', 'Mumbai', 'TEST', '05/02/2010'

union all

select 1, 'sameer1', 'Pune', 'TEST2', '08/04/2010'

;with cte as

(

select *, row_number() over(partition by a_id order by a_id) as [rep]

from #Multi

)

select a_add, a1_add, a3_add, a4_dt [a4_dt(1)],

(select a_add from cte where a_id = c1.a_id and rep = 2) [a_add],

(select a1_add from cte where a_id = c1.a_id and rep = 2) [a1_add],

(select a3_add from cte where a_id = c1.a_id and rep = 2) [a3_add],

(select a4_dt from cte where a_id = c1.a_id and rep = 2) [a4_dt(2)]

from cte c1 where rep = 1

A word of caution: multiple selects mean multiple reads from the table, this will involve table scan if you do not add indexes. I do not know why the table is designed so; I think it is crappy anyway. If you will have two rows for each a_id why don't you add some more columns to the table.

- arjun

https://sqlroadie.com/

SQLpilot SSC Enthusiast Points: 110 More actions · Answer 3

SQLpilot

SSC Enthusiast

Points: 110

June 8, 2010 at 8:48 am

#1177510

This should do what you need:

DECLARE @Result CHAR(8000)



SET @Result = ''

SELECT @Result = rtrim(@Result) + ' ' + ltrim(str(a_id)) + ' ' + a_add

+ ' ' + a1_add + ' ' + a3_add + ' ' + ltrim(convert(varchar,a4_dt,101))

FROM tbl_example

WHERE a_id = 1

PRINT @Result

KMPSSS Ten Centuries Points: 1052 More actions · Answer 4

hello friend,

Kindly try the below one.

Declare @t table

(

a_id int,

a_add varchar(20),

a1_add varchar(20),

a3_add varchar(20),

a4_dt varchar(20)

)

Insert into @t

Select 1 ,'sameer','Mumbai','TEST','05/02/2010' Union All

Select 1 ,'sameer1','Pune','TEST2','08/04/2010'

Select * from @t

declare @xml xml

set @xml=(Select t2.a_id ,t2.a_add ,t2.a1_add ,t2.a3_add ,t2.a4_dt from @t t2 where t2.a_id=1 for xml path(''))

Select

w.value('a_id[1]','varchar(50)') a_id,

w.value('a_add[1]','varchar(50)') a1_add,

w.value('a1_add[1]','varchar(50)') a1_add,

w.value('a3_add[1]','varchar(50)') a3_add,

w.value('a4_dt[1]','varchar(50)') a4_dt,

w.value('a_id[2]','varchar(50)') a_id,

w.value('a_add[2]','varchar(50)') a1_add,

w.value('a1_add[2]','varchar(50)') a1_add,

w.value('a3_add[2]','varchar(50)') a3_add,

w.value('a4_dt[2]','varchar(50)') a4_dt

from @xml.nodes('.') n(w)

Let me know that, wheather u got correct result