Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

customizing a query output Expand / Collapse
Author
Message
Posted Friday, October 23, 2009 7:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 11:29 PM
Points: 26, Visits: 69
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

Post #807881
Posted Friday, October 23, 2009 7:56 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:07 PM
Points: 579, Visits: 1,807
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



---------------------------------------------------------------------------------
Post #807903
Posted Friday, October 23, 2009 8:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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


Post #807964
Posted Friday, October 23, 2009 9:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 11:29 PM
Points: 26, Visits: 69
hi dear,
your query is actually producing result like
124 [a]jh
124 [b]ds
its actually appending the second column data
Post #808006
Posted Friday, October 23, 2009 10:31 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:07 PM
Points: 579, Visits: 1,807
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



---------------------------------------------------------------------------------
Post #808045
Posted Friday, October 23, 2009 7:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 11:29 PM
Points: 26, Visits: 69
dear when i am executing your query ,its producing result like


124 124(a)

two times its coming,plus second column is not coming
Post #808233
Posted Saturday, October 24, 2009 12:39 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:07 PM
Points: 579, Visits: 1,807
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



---------------------------------------------------------------------------------
Post #808262
Posted Saturday, October 24, 2009 1:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 11:29 PM
Points: 26, Visits: 69
thanks dear , my problem is solved
Post #808264
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse