CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Johann
Johann

Reputation: 69

query results need break line

The following sql code in the second column record tkt_no didn't break line, It still have one space between two tkt_nos, like AAA111 AAA112, how should I do to add a break line between two tkt_nos? please help me, thank you.

SELECT min(post_no) +'~'+ max(post_no) as post_no, min(tkt_no) + char(13)+char(10) + max(tkt_no) as tkt_no,  plic
FROM TEST  
WHERE date = '170711'
GROUP BY plic
ORDER BY post_no

This is the above sql code output:

post_no     | tkt_no      | plic
11001~11010 | AA001 AA010 | N
11011~11099 | AB001 AB089 | N
12000~12001 | BA000 BA001 | C
12002~12099 | BB001 BA098 | C

but I need my query output in the 'Result to grid' like below:

post_no     | tkt_no      | plic
11001~11010 | AA001       | N
              AA010
11011~11099 | AB001       | N
              AB089  
12000~12001 | BA000       | C
              BA001 
12002~12099 | BB001       | C
              BA098

please help me, thank you.

Upvotes: 0

Views: 75

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Assuming that you have dataset as below :

post_no     | tkt_no      | plic
11001~11010 | AA001 AA010 | N
11011~11099 | AB001 AB089 | N
12000~12001 | BA000 BA001 | C
12002~12099 | BB001 BA098 | C

& you would like to display in grid style as you have mentioned, then you could use xml node method from which you could split your data with grid style as below 

SELECT 
    case when row_number() over (partition by post_no order by (select 1)) > 1 then '' else post_no end post_no,
    a.value('.', 'VARCHAR(MAX)') [tkt_no],
    case when row_number() over (partition by post_no, plic order by (select 1)) > 1 then '' else plic end plic
    FROM 
(
    SELECT post_no, CAST('<A>'+REPLACE(tkt_no, ' ', '</A><A>')+'</A>' AS XML) AS  tkt_no, plic FROM <your data set>
) A CROSS APPLY tkt_no.nodes ('/A') as split(a)

Result :

post_no     tkt_no  plic
11001~11010 AA001   N
            AA010   
11011~11099 AB001   N
            AB089   
12000~12001 BA000   C
            BA001   
12002~12099 BB001   C
            BA098

Upvotes: 1

Related Questions