Search code examples
sqlsql-servert-sqlsql-server-2014

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.


Solution

  • 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