customizing a query output

  • 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

  • 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

    ---------------------------------------------------------------------------------

  • 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

  • hi dear,

    your query is actually producing result like

    124 [a]jh

    124 ds

    its actually appending the second column data

  • 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

    ---------------------------------------------------------------------------------

  • dear when i am executing your query ,its producing result like

    124 124(a)

    two times its coming,plus second column is not coming

  • 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

    ---------------------------------------------------------------------------------

  • 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