to print two or more rows of records in a single

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 2005 Forums

Transact-SQL (2005)

to print two or more rows of records in a single

Author

Topic

karthikeyan.marlen
Starting Member

16 Posts

Posted - 2011-11-24 : 07:37:40

Hi

I am having the table like

ID Name Extracurricular

1 aa karate

1 aa silambam

and i want to show it as

ID Name ExtraCurricular1 ExtraCurricular2
1 aa karate silambam

pls let me know me know the solution for this

Thanks

karthikeyan

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-24 : 07:58:24

select id, Name, ExtraCurricular1 = min(Extracurricular), ExtraCurricular2 = max(Extracurricular)
from tbl
group by id, Name

always 2 rows?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-24 : 08:02:25

[code]--ID Name Extracurricular
CREATE TABLE [dbo].[Table1](
[id] [int] NOT NULL,
[name] [varchar](50) NULL,
[extracurricular] [varchar](50) NULL)

--1 aa karate
--1 aa silambam
--2 bb karate

insert into dbo.table1
values ( 1,'aa','karate')
,( 1,'aa','silambam')
,( 2,'bb','karate')

select * from Table1

SELECT DISTINCT s1.id,s1.name,
STUFF((SELECT TOP 100 PERCENT ' ' + s2.[extracurricular] FROM table1 AS s2 WHERE s2.id = s1.id ORDER BY '' + s2.[extracurricular] FOR XML PATH('')), 1, 1, '') AS [extracurricular]
FROM table1 AS s1
ORDER BY s1.[name]

drop table Table1

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

karthikeyan.marlen
Starting Member

16 Posts

Posted - 2011-11-24 : 08:03:01

No two rows always,the row nos can be any rows, if( cursor technique is the solution for this),pls tell me that solution,thanks for replying

select id, Name, ExtraCurricular1 = min(Extracurricular), ExtraCurricular2 = max(Extracurricular)
from tbl
group by id, Name

always 2 rows?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
[/quote]

karthikeyan

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-24 : 08:12:15

What's wrong with the query I posted?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

karthikeyan.marlen
Starting Member

16 Posts

Posted - 2011-11-24 : 08:18:45

Hi,
No problem with query,It's ok when there is only two rows

if the no of rows is greater than 2,then ?

for eg ,i am having the table like

ID name extracurricular Marks

1 aa meditation 90

1 aa yoga 80

1 aa cricket 78

1 aa football 67

Then i want to view the above table as

ID Name extra1 extra2 extra3 extra4 marks1 marks2 marks3 marks4

1 aa medit yoga cricket foot 90 80 78 67

Thanks

What's wrong with the query I posted?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
[/quote]

karthikeyan

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-24 : 08:53:41

Sorry - didn't realise what you meant by No two rows always.

If you want a resultset with columns (a pivot) then I don't think it's possible without dynamic sql for an unknown number of columns.
If you are happy with a seperated string for each then

declare @table table (id int, name varchar(10), ExtraCurricular varchar(10), marks varchar(10))
insert @table select 1, 'aa', 'meditation', '90'
insert @table select 1, 'aa', 'yoga', '80'
insert @table select 1, 'aa', 'cricket', '78'
insert @table select 1, 'aa', 'football', '67'

select id, name,
ExtraCurricular = stuff (
(
select ';' + ExtraCurricular
from @table t2 where t.id = t2.id and t.name = t2.name
for xml path('')
)
,1,1,'') ,
marks = stuff (
(
select ';' + marks
from @table t2 where t.id = t2.id and t.name = t2.name
for xml path('')
)
,1,1,'')
from (select distinct id, name from @table) t

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

karthikeyan.marlen
Starting Member

16 Posts

Posted - 2011-11-24 : 09:05:01

Thanks for replying me

but I want the format like
ID name ECA1 MARKS1 ECA2 MARKS2 ECA3 MARKS3
1 aa silambm 90 medit 78 cricket 64

Sorry - didn't realise what you meant by No two rows always.

If you want a resultset with columns (a pivot) then I don't think it's possible without dynamic sql for an unknown number of columns.
If you are happy with a seperated string for each then

declare @table table (id int, name varchar(10), ExtraCurricular varchar(10), marks varchar(10))
insert @table select 1, 'aa', 'meditation', '90'
insert @table select 1, 'aa', 'yoga', '80'
insert @table select 1, 'aa', 'cricket', '78'
insert @table select 1, 'aa', 'football', '67'

select id, name,
ExtraCurricular = stuff (
(
select ';' + ExtraCurricular
from @table t2 where t.id = t2.id and t.name = t2.name
for xml path('')
)
,1,1,'') ,
marks = stuff (
(
select ';' + marks
from @table t2 where t.id = t2.id and t.name = t2.name
for xml path('')
)
,1,1,'')
from (select distinct id, name from @table) t

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
[/quote]

karthikeyan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources