CODEWITHSUNDEEP
htmlsqlsql-serverstring
Natalia Natalie
Natalia Natalie

Reputation: 677

SQL Server converting output to HTML

I have this code:

DECLARE 

 @CustomerID int = 8,
 @UtilityCompanyID int = 1

SELECT MD.SERV_ACCT, 
       AD.ACCOUNT,
       MD.SERV_ADDRESS
       ----AD.DOING_BUSINESS_AS,
       --MD.SERV_ACCT + ', ' + AD.ACCOUNT + ', ' + MD.SERV_ADDRESS AS ADB
FROM
(SELECT DISTINCT SERV_ACCT, SERV_ADDRESS, MAX(INV_DATE) as MAXINVDATE
FROM tblAPSData

WHERE SERV_ACCT NOT IN (SELECT ServiceAccount
            FROM tblMEP_Meters
            JOIN tblMEP_Sites
            ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

            JOIN tblMEP_Projects
            ON tblMEP_Projects.ID = tblMEP_Sites.ProjectID

            WHERE CustomerID = 8
            AND Type = 1
            )

AND ACCOUNT IN (SELECT AccountNumber
                FROM tblMEP_CustomerAccounts
                WHERE CustomerID = @CustomerID
                AND UtilityCompanyID = @UtilityCompanyID)

AND INV_DATE > DATEADD(month, -6, getdate())
GROUP BY SERV_ACCT, SERV_ADDRESS) MD
join tblAPSdata AD on MD.SERV_ACCT = AD.SERV_ACCT 
and MD.MAXINVDATE = AD.INV_DATE

This is the output:

SERV_ACCT   ACCOUNT         SERV_ADDRESS
0289S61288  117512280   921 N dsdijdsi
1435S01282  117512280   238 D dsdijads 
2498S21288  117512280   23814 4fksf 
5538S21284  117512280   4324 fjsdf
6109S12286  117512280   0912  fuehsf
7488S22289  117512280   242 fsjdhfs
8058S12287  117512280   309 fjsnef
9058S00288  117512280   342 fhsfuqq
9645S21281  117512280   556 dajdjawd
1440S13289  312937281   2383 hfsdufh
5384S92284  979437282   324 dhauda
0492S90281  117512280   29349 dhsadfsf
1303S90280  117512280   89e3y djasd 
1548S00286  117512280   3426 iodjad
7358S00281  117512280   39844 jdfasjf
9168S00282  117512280   399 ndfsfjk

I want the output formatted in HTML! Is that possible? Does anybody knows if SQL Server can do that? Or I have to code it manually? I read about string deliminator but I am not sure if this si what I really want to do, cause I do not even know how to do it.

Upvotes: 1

Views: 747

Answers (2)

Bernhard Hofmann
Bernhard Hofmann

Reputation: 10391

I'd hate someone to come along and see that this cannot be done (technically) in SQL, so here's the solution:

DECLARE 
    @CustomerID int = 8,
    @UtilityCompanyID int = 1

    ;
    WITH Html AS (
        SELECT 1 AS SortOrder,'<table>' AS Content
        UNION ALL SELECT 2,'<tr><th>SERV_ACCT</th><th>ACCOUNT</th><th>SERV_ADDRESS</th></tr>'
        UNION ALL SELECT 3,'<tr><td>'+ MD.SERV_ACCT +'</td><td>'+ AD.ACCOUNT +'</td><td>'+ MD.SERV_ADDRESS +'</td></tr>'
        FROM
(SELECT DISTINCT SERV_ACCT, SERV_ADDRESS, MAX(INV_DATE) as MAXINVDATE
FROM tblAPSData

WHERE SERV_ACCT NOT IN (SELECT ServiceAccount
            FROM tblMEP_Meters
            JOIN tblMEP_Sites
            ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

            JOIN tblMEP_Projects
            ON tblMEP_Projects.ID = tblMEP_Sites.ProjectID

            WHERE CustomerID = 8
            AND Type = 1
            )

AND ACCOUNT IN (SELECT AccountNumber
                FROM tblMEP_CustomerAccounts
                WHERE CustomerID = @CustomerID
                AND UtilityCompanyID = @UtilityCompanyID)

AND INV_DATE > DATEADD(month, -6, getdate())
GROUP BY SERV_ACCT, SERV_ADDRESS) MD
join tblAPSdata AD on MD.SERV_ACCT = AD.SERV_ACCT 
and MD.MAXINVDATE = AD.INV_DATE

        UNION ALL SELECT 4,'</table>'
    )
    SELECT Content FROM Html ORDER BY SortOrder

But this is not the most efficient method, and I would not recommend it for a website or web application.

Upvotes: 0

Michael Kazarian
Michael Kazarian

Reputation: 4462

No. You must combine SQL whith external tools (programming languages, CMS, batch-file, etc).

Upvotes: 1

Related Questions