How to create task in order to get one instead 3 column?

Question

Post reply

How to create task in order to get one instead 3 column?

Jagger-784276

SSC Eights!

Points: 808
More actions
March 21, 2010 at 1:12 pm

#230986

Hi,

I am beginner.Just an example. If I have table with 3 rows and 3 columns:

customer number notes last visit date

100000 text 1 10.feb.2010

100000 text 2 10.feb.2010

100000 text 3 10.feb.2010

How to make customer number with only one row like this:

100000 text 1 text 2 text 3 10.feb.2010

thanks?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

There are many different options for this in SQL 2005. See the link in my signature referencing string concatenation and post back with any questions.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 2

Try this:

create table #t(customernumber varchar(10), notes varchar(10), lastvisitdate smalldatetime)

insert into #t values(100000, 'text 1', '10.feb.2010')

insert into #t values(100000, 'text 2', '10.feb.2010')

insert into #t values(100000, 'text 3', '10.feb.2010')

;with cte as

(

select customernumber,

(

select notes + ' ' from #t

where

customernumber = t.customernumber

and lastvisitdate = t.lastvisitdate

for xml path('')

) 'notes',

lastvisitdate

from #t t

group by

customernumber, lastvisitdate

)

select customernumber + ' ' + notes + ' ' + convert(varchar,lastvisitdate,104) from cte

drop table #t

https://sqlroadie.com/

Jagger-784276 SSC Eights! Points: 808 More actions · Answer 3

Thanks Arjun,

but I still need all columns.

columns are:

customernumber

lastvisitdate

nr (1)

nr (2)

nr (3)

row -> 100000,10.feb.2010, New York, London, Paris

here u r:

use databasename

go

CREATE TABLE TEST2

(

Customernumber int NOT NULL,

LastVisitDate datetime NOT NULL,

Note varchar(200) NOT NULL,

Nr smallint NULL

)

GO

INSERT INTO TEST2

(Customernumber, LastVisitDate, Note,Nr)

SELECT 100000, N'10.feb.2010', N'New York',1 UNION ALL

SELECT 100000, N'10.feb.2010', N'London',2 UNION ALL

SELECT 100000, N'10.feb.2010', N'Paris',3

go

thanks

John McC Hall of Fame Points: 3410 More actions · Answer 4

You just need to tweak Arjuns code a bit



create table #t(customernumber varchar(10), notes varchar(10), lastvisitdate smalldatetime)

insert into #t values(100000, 'text 1', '10.feb.2010')

insert into #t values(100000, 'text 2', '10.feb.2010')

insert into #t values(100000, 'text 3', '10.feb.2010')

;with cte as

(

select customernumber,

(

select ', ' + notes   from #t

where

customernumber = t.customernumber

and lastvisitdate = t.lastvisitdate

for xml path('')

) 'notes',

lastvisitdate

from #t t

group by

customernumber, lastvisitdate

)

select customernumber + ', '  + convert(varchar,lastvisitdate,106) +  notes from cte

drop table #t

Changing the date format to 106 gives the shortened month name but loses the German dd.mm.yy format

100000, 10 Feb 2010, text 1, text 2, text 3

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 5

Did that solve your problem Jagger?

I don't quite understand what you mean by

but I still need all columns.

columns are:

customernumber

lastvisitdate

nr (1)

nr (2)

nr (3)

row -> 100000,10.feb.2010, New York, London, Paris

If the row thingy is the output you need, then John answers your question.

If not please mention how the output should be.

Thanks

Arjun

https://sqlroadie.com/

Jagger-784276 SSC Eights! Points: 808 More actions · Answer 6

Jagger-784276

SSC Eights!

Points: 808

March 24, 2010 at 5:45 am

#1138809

Hi,

Output should be in separate columns per row.

customernumber lastvisit date 1 2 3

100000 10.feb.2010 New York London Paris

On your way I have it all in only one column.

I hope that u understand me.

regards

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 7

If you want the data in separate columns, try this:

create table #t(customernumber varchar(10), notes varchar(10), lastvisitdate smalldatetime, nr int)

insert into #t values(100000, 'text 1', '10.feb.2010',1)

insert into #t values(100000, 'text 2', '10.feb.2010',2)

insert into #t values(100000, 'text 3', '10.feb.2010',3)

select piv.customernumber, convert(varchar,lastvisitdate,104) 'date',

notes1 = piv.[1],

notes2 = piv.[2],

notes3 = piv.[3]

from

(

select customernumber, lastvisitdate, notes,

rn = row_number() over (partition by customernumber order by lastvisitdate)

from #t

) rows

pivot

(

max(notes) for

rn in ([1],[2],[3])

)piv;

drop table #t

https://sqlroadie.com/

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 8

DROP TABLE #t



create table #t(customernumber varchar(10), notes varchar(10), lastvisitdate smalldatetime)

insert into #t values(100000, 'text 1', '10.feb.2010')

insert into #t values(100000, 'text 2', '10.feb.2010')

insert into #t values(100000, 'text 3', '10.feb.2010')

select customernumber+', '+CONVERT(VARCHAR(20),lastvisitdate,106)+' '+

(

select ', ' + notes   from #t

where

customernumber = t.customernumber

and lastvisitdate = t.lastvisitdate

for xml path('')

)

from #t t

group by

customernumber, lastvisitdate