Formatting SQL Server Query Output

Question

I like to get the SQL Server query output in the following way. Currently my output is:

Class | Student ID | Student Name
------+------------+---------------
121        S1          Test 1
121        S2          Test 2
500        S22         Test a
500        S23         Test b

But I want the data output in the following way -

Class: 121
--------------------------------

Student ID | Student Name
-----------+---------------
S1            Test 1
S2            Test 2

Class: 500
--------------------------------

Student ID | Student Name
-----------+---------------
S22            Test a
S23            Test b

Could someone help me how can I achieve these?

Thanks

To my knowledge you can't achieve this exact result with just SQL. Are you sending this data to an application like SSRS after it is returned? This is fairly simple to do in SSRS. — McGlothlin
– McGlothlin, Commented Jul 7, 2017 at 22:34
@McGlothlin, I am not sending to SSRS, I will use this query to my asp.net project — APC
– APC, Commented Jul 7, 2017 at 23:03
You would not configure this output in SQL Server. This would have to be configured in your asp.net project. — Andrew O'Brien
– Andrew O'Brien, Commented Jul 7, 2017 at 23:16

Jay Wheeler · Accepted Answer · 2017-07-08 03:22:42Z

While I would agree SQL isn't the place to do your formatting.... sometimes you get stuck....

This Code

select '121' as Class,       'S1  ' as [Student ID],        'Test 1' as [Student Name] into #test
union select '121', 'S2  ', 'Test 2'
union select '500', 'S22 ', 'Test a'
union select '500', 'S23 ', 'Test b'

; 
WITH class
AS (
    SELECT DISTINCT class
    FROM #test
    )
    ,student
AS (
    SELECT class
        ,(
            SELECT STUFF((
                        SELECT CHAR(13) + t.[Student ID] + space(len('Student ID | ') - len(t.[Student ID])) + [Student Name]
                        FROM #Test t
                        WHERE t.[class] = class.[class]
                        FOR XML PATH('')
                            ,type
                        ).value('.[1]', 'nvarchar(max)'), 1, 1, '')
            ) AS info
    FROM class
    )
    ,theoutput
AS (
    SELECT 'Class: ' + class + CHAR(13) + '------------------------' + CHAR(13) + 'Student ID | Student Name' + CHAR(13) + info + CHAR(13) + CHAR(13) AS txt
    FROM student c
    )
SELECT STUFF((
            SELECT CHAR(13) + txt
            FROM theoutput
            FOR XML PATH('')
                ,type
            ).value('.[1]', 'nvarchar(max)'), 1, 1, '')

Will Produce

Class: 121
------------------------
Student ID | Student Name
S1            Test 1
S2            Test 2


Class: 500
------------------------
Student ID | Student Name
S22          Test a
S23          Test b

user7715598 · Accepted Answer · 2017-07-10 12:17:27Z

By using While loop we can get the Desired result Created sample data

IF OBJECT_ID('Tempdb..#TEMP') IS NOT NULL
Drop table #TEMP
;With cte(Class, StudentID, StudentName)
AS
(
SELECT 121,'S1' ,'Test 1' UNION ALL
SELECT 121,'S2' ,'Test 2' UNION ALL
SELECT 500,'S22','Test a' UNION ALL
SELECT 500,'S23','Test b'
)               
SELECT * INTO #TEMP FROM  CTe

SET NOCOUNT ON
DECLARE @CLassCOUnt INT
    ,@minID INT
    ,@maxid INT
    ,@Sql NVARCHAR(max)
    ,@SelectQuery INT
DECLARE @Table TABLE (
    ID INT IDENTITY
    ,CLass INT
    )

INSERT INTO @Table (CLass)
SELECT DISTINCT Class
FROM #TEMP

SELECT @minID = min(Id)
FROM @Table

SELECT @maxid = MAX(Id)
FROM @Table

WHILE (@minID <= @maxid)
BEGIN
    SELECT @SelectQuery = Class
    FROM @Table
    WHERE ID = @minID

    SELECT @Sql = 'SELECT Class, StudentID, StudentName FROM #TEMP WHERE Class=' + CAST(@SelectQuery AS VARCHAR)


    PRINT 'Result Of Class:'+ CAST(@SelectQuery AS VARCHAR)+CHAR(13)+CHAR(10)+'------------------------'
    PRINT @Sql
    EXEC (@Sql)

    SET @minID = @minID + 1
END

SET NOCOUNT OFF

Collectives™ on Stack Overflow

Formatting SQL Server Query Output

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related