October 23, 2009 at 7:39 am
Hi guys ,
I have a problem.I am taking the resultset of a query in a text file.The query prints out the Workorder and related data.Now the problem is sometimes for one workoreder we have two related data like
workorder relatedata
122 asas
123 der
124 wer3er
124 rtre
Now the client wants the resultset such that when we have same workorders they should be prefixed with a alphabet like
workorder relatedata
122 asas
123 der
124(a) wer3er
124 (b) rtre
one more thing at a time we wont have more than five same workorders
Please guys if u can come across some soution then please let me know
October 23, 2009 at 7:56 am
Will this do?
CREATE TABLE #WO(WO int, RD varchar(10))
INSERT INTO #WO VALUES (122 ,'asas')
INSERT INTO #WO VALUES (123 ,'der')
INSERT INTO #WO VALUES (124 ,'wer3er')
INSERT INTO #WO VALUES (124 ,'rtre')
INSERT INTO #WO VALUES (124 ,'hehehe')
SELECT *, CASE WHEN Row_no > 1 THEN
CONVERT(varchar, WO)+ CHAR(63 + Row_no)
ELSE CONVERT(varchar,WO) END FROM
(Select * ,
Row_Number() OVER (Partition BY WO ORDER BY WO) as Row_No
FROM #WO) #t1
---------------------------------------------------------------------------------
October 23, 2009 at 8:53 am
This seems like something that would be better handled in the front-end application, but anyway here is my solution (with create table code borrowed from above)
CREATE TABLE #WO(WO int, RD varchar(10))
INSERT INTO #WO VALUES (122 ,'asas')
INSERT INTO #WO VALUES (123 ,'der')
INSERT INTO #WO VALUES (124 ,'wer3er')
INSERT INTO #WO VALUES (124 ,'rtre')
SELECT WO, CASE WHEN RowCnt > 1 THEN
'(' + CHAR(96 + Row_no) + ') ' + RD
ELSE RD END FROM
(Select * ,
Row_Number() OVER (Partition BY WO ORDER BY WO) as Row_No
FROM #WO) #t1 INNER JOIN (SELECT wo AS Wo1,COUNT(*) AS RowCnt FROM #wo GROUP BY WO) a ON #t1.WO = a.wo1
October 23, 2009 at 9:49 am
hi dear,
your query is actually producing result like
124 [a]jh
124 ds
its actually appending the second column data
October 23, 2009 at 10:31 am
This wont help you?
SELECT *, CASE WHEN Row_no > 1 THEN
CONVERT(varchar, WO)+ CHAR(63 + Row_no)
ELSE CONVERT(varchar,WO) END FROM
(Select * ,
Row_Number() OVER (Partition BY WO ORDER BY WO) as Row_No
FROM #WO) #t1
Ohh sorry, Steve is right, thanks Steve. This should work for you Sunil! (Steve was using RD instead of WO)
SELECT WO, CASE WHEN RowCnt > 1 THEN
CONVERT(varchar, WO) + '(' + CHAR(96 + Row_no) + ') '
ELSE CONVERT(varchar, WO) END FROM
(Select * ,
Row_Number() OVER (Partition BY WO ORDER BY WO) as Row_No
FROM #WO) #t1
INNER JOIN (SELECT wo AS Wo1,COUNT(*) AS RowCnt FROM #wo GROUP BY WO) a
ON #t1.WO = a.wo1
---------------------------------------------------------------------------------
October 23, 2009 at 7:48 pm
dear when i am executing your query ,its producing result like
124 124(a)
two times its coming,plus second column is not coming
October 24, 2009 at 12:39 am
I think you are not trying to understand what your query is doing! just put whatever column you want in your select clause!!
SELECT CASE WHEN RowCnt > 1 THEN
CONVERT(varchar, WO) + '(' + CHAR(96 + Row_no) + ') '
ELSE CONVERT(varchar, WO) END, RD FROM
(Select * ,
Row_Number() OVER (Partition BY WO ORDER BY WO) as Row_No
FROM #WO) #t1
INNER JOIN (SELECT wo AS Wo1,COUNT(*) AS RowCnt FROM #wo GROUP BY WO) a
ON #t1.WO = a.wo1
---------------------------------------------------------------------------------
October 24, 2009 at 1:21 am
thanks dear , my problem is solved
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply