HI
I am having one table.
in that columns are trid and remarks.
the data is:
trid | remarks
-------
1 travel:comment1
1 travel:comment2
1 emp:emp comment1
1 emp:emp comment2
so i want to display query like in this below format:having 3 columns,i.e trid, travel remarks, empremarks and travelremarks should display data start with 't' , empremarks data should display data starts with 'e'.
I am getting data start with 't' and 'e',but how to combine travel data, emp data. and it should display in one row itself instead of 4 rows for every trid '1'.
trid | travelremarks | emp remarks
-------------------------------------------
1 travel:comment1,travel:comment2 emp:emp comment1,emp:emp comment2
please help me.
Thanks,
swapprose.
SQL Server 2005 solution:
SELECT pvt.trid, pvt.traas'travelremarks', pvt.empas'emp remarks'FROM
(SELECT pt.trid, pt.remarks_left3, pt.[1]+coalesce(','+ pt.[2],'')as Merged
from(SELECT trid,LEFT(remarks, 3)AS remarks_left3, remarks, ROW_NUMBER()OVER(PARTITIONBYLEFT(remarks, 3)ORDERBY remarks)as seqFROM yourTable_2)as t
PIVOT(min(remarks)FOR seqin([1], [2]))as pt)as t1
PIVOT(MIN(t1.Merged)FOR t1.remarks_left3IN([tra], [emp]))AS pvt
|||Hi
Thank you very much.
but i want query in sql server 2000.
its giving error like rownumber() not existing.
so can u plz make query in sql server 2000.
and the data which i gave was only sample data.
i think u done query by considering only 4 rows .i want in generalised way.
i am not getting where r u finding out travel remarks column should display data starts with 't'
and emp rmarks column data should display data starts with 'e'. b'coz i want to split remarks into travel and emp remarks given by travel and emp's.
i want query in a generalised way,b'coz table contains so many trid's ,so many travel remarks,emp
remarks data.
the table is like,just i m giving sample data.so it contains so many trid's ,remarks
trid | remarks
---------
1 travel:option given
1 emp :some comments
2 emp :trid select
2 travel:chosen
1 emp :option1
3 travel:set id
4 emp :choose
5 emp :abc
so i want in generalised way. the order of remarks may come in any way.
please consider all my points.
i m waiting for ur reply.
Thanks,
swapprose.
|||try this one for sql server 2000:
SELECT t3.trid,max(case t3.remarks_left3when'emp'then t3.remarksend)as emp_category
,max(case t3.remarks_left3when'tra'then t3.remarksend)as travel_category
FROM(select tr.trid, tr.remarks_left3,max(case tr.seqwhen 1then tr.remarksend)
+max(case tr.seqwhen 2then', '+ tr.remarkselse''end)
+max(case tr.seqwhen 3then', '+ tr.remarkselse''end)
+max(case tr.seqwhen 4then', '+ tr.remarkselse''end)
+max(case tr.seqwhen 5then', '+ tr.remarkselse''end)as remarks
from
(select trid,LEFT(remarks, 3)AS remarks_left3, remarks,
(selectcount(*)from yourTable_2as t2
whereLEFT(t2.remarks, 3)=LEFT(t1.remarks, 3)
AND t2.remarks<= t1.remarks)as seqfrom yourTable_2AS t1)as tr
groupby tr.trid, tr.remarks_left3)AS t3
GROUPBY t3.trid
|||Hi
Thank you very much for ur solution.When my table is having only trid no. 1 , its coming properly.
But the thing is when my table is having trid's like 1,2,3,4, then its not working.
when i tried by using trid's no. 1,then its coming.Fine.
if i have trid no's 1,2,3,4 its not coming.
i want in generalised way.plz assume table is having so many trid's,remarks and so many rows.
suppose my table is having data like:
trid remarks
--------
1 emp:aa
1 emp:bb
1 travel:cc
1 travel:dd
2 travel:ee
2 travel:ff
2 travel:gg
2 emp:hh
3 emp:ftgfdg
3 emp:cvgxcv
4 travel:rrtt
can you plz try it once by table having trid's like 1,2,3-- so on.
plz help me.
i m waiting for ur solution.
thanks a lot for ur immediate reply and help.
|||
Hi,
Show the exact result set you want for your sample data set. Do you combine all ids together for one big row?
|||HI
I'll explain my problem clearly.
assume this is my table having sample data.
trid remarks
--------
1 emp:aa
1 emp:bb
1 travel:cc
1 travel:dd
this works fine .result is like
but when i include data in table like below:
trid travel remarks emp remarks
1 travel:cc,travel:dd emp:aa,emp:bb
--------------------------------------------------------------
when i included more trid's,its not working
trid remarks
--------
1 emp:aa
1 emp:bb
1 travel:cc
1 travel:dd
2 travel:ee
2 travel:ff
2 travel:gg
2 emp:hh
3 emp:ftgfdg
3 emp:cvgxcv
4 travel:rrtt
now table contains trid's like 1,2,3,4 ,then result is not proper.
result is SHOWING like:
trid travel remarks emp remarks
1 travel:cc,travel:dd emp:aa,emp:bb
2 NULL NULL
3 NULL NULL
4 NULL NULL
could u plz tell me the solution.
bye
swapprose.
|||Here are the fixed solutions for both SQL Server 2000 and 2005:
--SQL Server 2000
SELECT t3.trid,max(case t3.remarks_left3when'emp'then t3.remarksend)as emp_category
,max(case t3.remarks_left3when'tra'then t3.remarksend)as travel_category
FROM(select tr.trid, tr.remarks_left3,max(case tr.seqwhen 1then tr.remarksend)
+max(case tr.seqwhen 2then', '+ tr.remarkselse''end)
+max(case tr.seqwhen 3then', '+ tr.remarkselse''end)
+max(case tr.seqwhen 4then', '+ tr.remarkselse''end)
+max(case tr.seqwhen 5then', '+ tr.remarkselse''end)as remarks
from
(select trid,LEFT(remarks, 3)AS remarks_left3, remarks,
(selectcount(*)from yourTable_2as t2
where t1.trid=t2.tridANDLEFT(t2.remarks, 3)=LEFT(t1.remarks, 3)
AND t2.remarks<= t1.remarks)as seqfrom yourTable_2AS t1)as tr
groupby tr.trid, tr.remarks_left3)AS t3
GROUPBY t3.trid
--SQL Server 2005
SELECT pvt.trid, pvt.traas'travelremarks', pvt.empas'emp remarks'FROM
(SELECT pt.trid, pt.remarks_left3, pt.[1]+coalesce(','+ pt.[2],'')as Merged
from(SELECT trid,LEFT(remarks, 3)AS remarks_left3, remarks, ROW_NUMBER()OVER(PARTITIONBY trid,LEFT(remarks, 3)ORDERBY remarks)as seqFROM yourTable_2)as t
PIVOT(min(remarks)FOR seqin([1], [2]))as pt)as t1
PIVOT(MIN(t1.Merged)FOR t1.remarks_left3IN([tra], [emp]))AS pvt
--The result is:
1 travel:cc,travel:dd emp:aa,emp:bb
2 travel:ee,travel:ff emp:hh
3 NULL emp:cvgxcv,emp:ftgfdg
4 travel:rrtt NULL
HI
Thanks a lot.
Now its working fine for everything.
if u don't mind can u explain me logic.
once again Thank you very much for your immediate help.
Bye,
swapprose.
|||suppose my table is having data like.
trid pnrno isoptionchosen
------------------
1 100 null
1 101 1
1 102 null
2 200 1
2 201 null
i want query like below.
o1pnrno | o1isoptionchosen | o2pnrno | o2isoptionchosen | o3pnrno | o3isoptionchosen | chosenpnrno | chosenisoptionchosen
--------------------------------------------------------------
100 null 101 1 102 null 101 1
200 1 201 null null null 200 1
so i want to display fields from same table accroding to trid's.
first trid='1' : it is having 3 rows,that 3 rows data should display and last isoptionchosen=1 that row data should display.
for trid=2 :its is having 2 rows.that 2 rows data should display and whatever ischosenoption=1 that row should display
Please help me.
Thanks,
bye
swapprose.
|||SELECT tr.trid,max(case tr.seqwhen 1then tr.pnrnoend)AS pnrno1,max(case tr.seqwhen 1then tr.isoptionchosenend)AS isoptionchosen1,
max(case tr.seqwhen 2then tr.pnrnoend)AS pnrno2,max(case tr.seqwhen 2then tr.isoptionchosenend)AS isoptionchosen2,
max(case tr.seqwhen 3then tr.pnrnoend)AS pnrno3,max(case tr.seqwhen 3then tr.isoptionchosenend)AS isoptionchosen3,
max(case tr.isoptionchosenwhen 1then tr.pnrnoend)AS Chosenpnrno, 1AS Chosenisoptionchosen
FROM(SELECT t2.trid, t2.pnrno, t2.isoptionchosen,(selectcount(*)from yourTable_2as t1where t1.trid=t2.tridAND t1.pnrno<= t2.pnrno)as seq
FROM(select trid, pnrno, isoptionchosenfrom yourTable_2) t2) tr
GROUPBY tr.trid
-- Or
SELECT tt.trid, tt.pnrno1, tt.isoptionchosen1, tt.pnrno2, tt.isoptionchosen2, tt.pnrno3, tt.isoptionchosen3, t4.chosenpnrno, t4.chosenisoptionchosenFROM(SELECT tr.trid,max(case tr.seqwhen 1then tr.pnrnoend)AS pnrno1,max(case tr.seqwhen 1then tr.isoptionchosenend)AS isoptionchosen1,
max(case tr.seqwhen 2then tr.pnrnoend)AS pnrno2,max(case tr.seqwhen 2then tr.isoptionchosenend)AS isoptionchosen2,
max(case tr.seqwhen 3then tr.pnrnoend)AS pnrno3,max(case tr.seqwhen 3then tr.isoptionchosenend)AS isoptionchosen3
FROM(SELECT t2.trid, t2.pnrno, t2.isoptionchosen,
(selectcount(*)from yourTable_2as t1where t1.trid=t2.tridAND t1.pnrno<= t2.pnrno)as seq
FROM(select trid, pnrno, isoptionchosenfrom yourTable_2) t2) tr
GROUPBY tr.trid) tt
INNERJOIN
(SELECT trid, pnrnoas chosenpnrno, isoptionchosenas chosenisoptionchosenFROM yourTable_2WHERE isoptionchosen=1)AS t4
ON t4.trid=tt.trid
|||Hi
Thanks a lot.
i have one more query.i.e
can u show seq.number like 1,2 ,3 for this result.
Thanks,
swapprose.
|||
SELECT tr.trid,max(case tr.seqwhen 1then tr.pnrnoend)AS pnrno1,max(case tr.seqwhen 1then tr.isoptionchosenend)AS isoptionchosen1, 1as seq1,
max(case tr.seqwhen 2then tr.pnrnoend)AS pnrno2,max(case tr.seqwhen 2then tr.isoptionchosenend)AS isoptionchosen2, 2as seq2,
max(case tr.seqwhen 3then tr.pnrnoend)AS pnrno3,max(case tr.seqwhen 3then tr.isoptionchosenend)AS isoptionchosen3, 3as seq3,
max(case tr.isoptionchosenwhen 1then tr.pnrnoend)AS Chosenpnrno, 1AS Chosenisoptionchosen
FROM(SELECT t2.trid, t2.pnrno, t2.isoptionchosen,(selectcount(*)from yourTable_2as t1where t1.trid=t2.tridAND t1.pnrno<= t2.pnrno)as seq
FROM(select trid, pnrno, isoptionchosenfrom yourTable_2) t2) tr
GROUPBY tr.trid
|||HI
Thanks for ur reply.
but my question is something different.
suppose my table is having data like
empname course
-----------
john m.s
rechel b.e
jack p.g
so i wnat sequence no. for these rows.
like
sequence no. empname course
-----------------
1 john m.s
2 recehl b.e
3 jack p.g
Thanks.
swapprose.
