SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


customizing a query output


customizing a query output

Author
Message
sunil88_pal88
sunil88_pal88
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 98
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
Nabha
Nabha
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2939 Visits: 1815
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



---------------------------------------------------------------------------------
steveb.
steveb.
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17060 Visits: 7195
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



sunil88_pal88
sunil88_pal88
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 98
hi dear,
your query is actually producing result like
124 [a]jh
124 [b]ds
its actually appending the second column data
Nabha
Nabha
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2939 Visits: 1815
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



---------------------------------------------------------------------------------
sunil88_pal88
sunil88_pal88
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 98
dear when i am executing your query ,its producing result like


124 124(a)

two times its coming,plus second column is not coming
Nabha
Nabha
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2939 Visits: 1815
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



---------------------------------------------------------------------------------
sunil88_pal88
sunil88_pal88
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 98
thanks dear , my problem is solved
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search