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.
| Author |
Topic |
|
Lambik
Starting Member
13 Posts |
 Posted - 2010-11-05 : 06:30:08
|
| I have a table in which text are in multiple rows. I want to display it in one row. see my example (sorry for my bad english)CREATE TABLE [dbo].[Table1]( [id] [int] NOT NULL, [person] [varchar](50) NULL, [date] [date] NULL, [text] [varchar](50) NULL, [text_id] [varchar](2) NULL, CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]insert into dbo.table1values ( 1,'Erik', '2010-05-06','sample text1','1') ,( 2,'Erik', '2010-05-06','sample text2','2') ,( 3,'John', '2010-07-08','sample text1','1') ,( 4,'John', '2010-07-08','sample text2','2') ,( 5,'John', '2010-07-08','sample text3','3')I want a query with the result:person date textErik 2010-05-06 sample text1sample text2John 2010-07-08 sample text1sample text2sample text3what I now have is a sql with group by but that does not do the job for me.Anyone who can help me?Lambik |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-05 : 06:40:25
|
| For each [person] you want columns from the row with the highest [id]? |
 |
|
|
Lambik
Starting Member
13 Posts |
Posted - 2010-11-05 : 06:43:02
|
quote:
Originally posted by Kristen
For each [person] you want columns from the row with the highest [id]?
no I want all the information but not in more rows but in one row. I want the information in the text column concatenated.Lambik |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-11-05 : 06:58:44
|
[code]SELECT DISTINCT s1.person,s1.[date], STUFF((SELECT TOP 100 PERCENT '' + s2.[text] FROM table1 AS s2 WHERE s2.person = s1.person ORDER BY '' + s2.[text] FOR XML PATH('')), 1, 1, '') AS [text]FROM table1 AS s1ORDER BY s1.person[/code]Adapted from Source (thx Peso): http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254edit: comma dropped since OP don't want it.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Lambik
Starting Member
13 Posts |
Posted - 2010-11-05 : 09:20:38
|
| thx Webfred this works great |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-11-05 : 09:22:38
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
group by