Post reply

T-SQL Code Rows in single result format

  • July 26, 2019 at 6:43 pm

    #3666436 

    CREATE TABLE #TEMP ( ACCOUNT_NO decimal(13,0), RATE_PLAN_NO smallint,  
    ACTIVESERVICES smallint,  TOTSERVICES    smallint)
    INSERT INTO #TEMP ( ACCOUNT_NO, RATE_PLAN_NO, ACTIVESERVICES, TOTSERVICES) 
    VALUES (286001, 198,10,10)
    INSERT INTO #TEMP ( ACCOUNT_NO, RATE_PLAN_NO, ACTIVESERVICES, TOTSERVICES) 
    VALUES (286001, 385,1,1)
    INSERT INTO #TEMP ( ACCOUNT_NO, RATE_PLAN_NO, ACTIVESERVICES, TOTSERVICES) 
    VALUES (286001, 386,1,1)
    INSERT INTO #TEMP ( ACCOUNT_NO, RATE_PLAN_NO, ACTIVESERVICES, TOTSERVICES) 
    VALUES (286002, 486,2,2)
    SELECT * FROM #TEMP

    SELECT CAST(ACCOUNT_NO AS VARCHAR) + ' U' + CAST(RATE_PLAN_NO  AS VARCHAR) + 
    '(' + CAST(ACTIVESERVICES  AS VARCHAR)  +')' + '(' + CAST(TOTSERVICES  AS 
    VARCHAR)  +')'
    FROM #TEMP 
    GROUP BY ACCOUNT_NO,RATE_PLAN_NO,ACTIVESERVICES,TOTSERVICES
    ORDER BY ACCOUNT_NO,RATE_PLAN_NO

    With Above Select I am very close but not 100%

    ACCOUNT_NO RATE_PLAN_NO ACTIVESERVICES TOTSERVICES

    286001. 198 10 10

    286001. 385 1 1

    286001. 386 1 1

    I need this report in the following format,

    286001 U198(10)(10) , U385(1)(1), U386(1) (1)

  • July 26, 2019 at 8:22 pm

    #3666457

    Here is the answer I found by myself for anyone out looking there,,,

    SELECT ACCOUNT_NO, RATE_PLAN_NO = STUFF((SELECT N', ' + ' U' + CAST(RATE_PLAN_NO  AS VARCHAR) + '(' + CAST(ACTIVESERVICES  AS VARCHAR)  +')' + '(' + CAST(TOTSERVICES  AS VARCHAR)  +')'
      FROM #TEMP AS p2
       WHERE p2.ACCOUNT_NO  = p.ACCOUNT_NO 
       ORDER BY RATE_PLAN_NO
       FOR XML PATH(N'')), 1, 2, N'')
    FROM #TEMP AS p
    GROUP BY ACCOUNT_NO 
    ORDER BY ACCOUNT_NO ;

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply