query format - SQL Server Forums

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)

query format

Author

Topic

karthikeyan.marlen
Starting Member

16 Posts

Posted - 2011-11-26 : 03:45:31

HI
I am having the table

sno name subj marks
1 aa tamil 90
1 aa english 80
2 bb tamil 90
2 bb sanskrit 100
2 bb maths 100

and i want the above table in the format

sno name subj1 mark1 subj2 mark2 subj3 mark3
1 aa tamil 90 english 80 null null
2 bb tamil 100 sanskrit 100 maths 100

pls tell me the query or storedprocedure for the above tasks
for the past three days,i am searching the solution for this,but didn't got yet

karthikeyan

james_wells
Yak Posting Veteran

55 Posts

Posted - 2011-11-26 : 07:43:48

i got the resukts in descending order

1 Fred maths 100 french 100 german 100
1 me maths 80 french 60 german 20

--------------------------------------------
-- created a temp table to match your table
---------------------------------------------

declare @temptable table
(
smo int,
name nvarchar(10),
subj nvarchar(10),
mark int
)

------------------------------------------
-- populated with results
------------------------------------------

insert into @temptable
values (1,'Fred','maths',100)

insert into @temptable
values (1,'Fred','french',100)

insert into @temptable
values (1,'Fred','german',100)

insert into @temptable
values (1,'me','maths',80)

insert into @temptable
values (1,'me','french',60)

insert into @temptable
values (1,'me','german',20)

select smo , name , subj , mark
from @temptable

----------------------------------------------------
-- my solution
----------------------------------------------------

--- create temp table -----

declare @temptable2 table
(
smo int,
name nvarchar(10),
comb nvarchar(1000)
)

-- pop with group data smo / name------

insert into @temptable2
select smo, name, ''
from @temptable
group by smo , name

--- temp cursor values

DECLARE @smo int
DECLARE @name nvarchar(10)
DECLARE @subj nvarchar(10)
DECLARE @mark int

---- create cursor

declare getdata cursor
for
select smo , name , subj , mark
from @temptable
order by smo , name , mark desc

open getdata

FETCH NEXT FROM getdata INTO @SMO , @NAME , @SUBJ , @MARK
WHILE @@FETCH_STATUS=0
BEGIN

--- this bit appends the data

update @temptable2
set comb = comb + @subj + ' ' + cast(@mark as nvarchar(3)) + ' '
where
@smo = smo and
@name = name

FETCH NEXT FROM getdata INTO @SMO, @NAME , @SUBJ , @MARK

END

CLOSE getdata
DEALLOCATE getdata

---- print result

select * from @temptable2

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-26 : 07:53:45

;with cte as
(
select *, seq = row_number() over (partition by sno order by marks desc, name desc)
from tbl
)
select t1.sno, t1.name ,
subj1 = t1.subj, mark1 = t1.marks ,
subj2 = t2.subj, mark2 = t2.marks ,
subj3 = t3.subj, mark3 = t3.marks ,
from cte t1
left join cte t2
on t1.sno = t2.sno and t2.seq = 2
left join cte t3
on t3.sno = t3.sno and t3.seq = 3
where t1.seq = 1
order by t1.sno

If you wanted a separated string for the marks and subjects then xpath will do it in a single statement.

==========================================
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.

james_wells
Yak Posting Veteran

55 Posts

Posted - 2011-11-26 : 19:01:33

Why do people put stupid remarks about other members specially when we are all trying to help

i am refering to "cursors are fine if you don't know SQL"

let me retort

Nigel there is a good reason why i did not choose to use cte as my solution especially the way you wrote it you need a join statemnet for every subject / mark combo in the source table

your code does not compile - remove the last , before the from
plus if you execute the corrected code you posted and you will see that the result set is incorrect

Please do not make assumption about fellow members

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-27 : 00:56:18

James, the comment about cursors is part of Nigel's auto-signature. You will see that in every one of his posts. Having seen hundreds, if not thousands of his posts, I feel confident in saying that he would not/did not intent to offend or insult you.

I have not studied neither your solution nor Nigel's solution, so I am not commenting specifically about either of your postings, but you will agree that more often than not, you see cursors used in contexts where a set based solution would be far superior. In that sense, there is a grain of truth (or perhaps a bushel of truth) to Nigel's signature.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources