SQL - format output

Question

Can someone guide me on formatting the below SQL query to print in the following format please?

------------------------
COUNT_NAME | COUNT_VAL |
------------------------
COUNT_A    | 1         | 
COUNT_B    | 2         |  
COUNT_C    | 3         |  
COUNT_D    | 4         |
------------------------

The below query returns output in the below format

--------------------------------
COUNT_A|COUNT_B|COUNT_C|COUNT_D|
--------------------------------
1      |2      |3      |4
--------------------------------

SQL QUERY

SELECT t1.COUNT_A, 
       t2.COUNT_B,
       t3.COUNT_C,
       t4.COUNT_D 
FROM
   (SELECT COUNT(INS_NAME) as COUNT_A 
    from table_A 
    where INS_NAME IN 
           (SELECT INS_NAME 
            from table_B 
            WHERE INS_ID IN 
                 (SELECT INS_MAP_ID 
                  FROM TEN_TO_INST_MAP 
                  where T_IN_MAP_ID = 
                     (SELECT T_ID 
                      FROM TW 
                      WHERE TNAM = 'abc')
                  )
            ) 
   AND T_DATE between '2015-01-01' and '2015-07-01' 
   AND INS_NAME NOT LIKE 'x%pr%' 
   AND INS_NAME like 'x%y%' 
   AND INS_NAME not like 'x%y%z') t1
,
(SELECT COUNT(INS_NAME) as COUNT_B 
 from table_A 
 where INS_NAME IN 
             (SELECT INS_NAME 
              from table_B 
              WHERE INS_ID IN 
                 (SELECT INS_MAP_ID 
                  FROM T_IN_MAP 
                  where T_IN_MAP_ID = (SELECT T_ID 
                                       FROM TW WHERE TNAM = 'abc')
                 )
             ) 
    AND T_DATE between '2015-01-01' and '2015-07-01' 
    AND INS_NAME NOT LIKE 'x%pr%' 
    AND INS_NAME like 'x%as%') t2
,
(SELECT COUNT(INS_NAME) as COUNT_C 
 from table_A 
 where INS_NAME IN 
      (SELECT INS_NAME 
       from table_B 
       WHERE INS_ID IN 
            (SELECT INS_MAP_ID 
             FROM T_IN_MAP 
             where T_IN_MAP_ID = (SELECT T_ID 
                                  FROM TW 
                                  WHERE TNAM = 'abc')
            )
       ) 
 AND T_DATE between '2015-01-01' and '2015-07-01' 
 AND INS_NAME NOT LIKE 'x%pr%' 
 AND INS_NAME like 'x%dfg%') t3
,
(SELECT COUNT(INS_NAME) as COUNT_C 
 from table_A 
 where INS_NAME IN 
           (SELECT INS_NAME 
            from table_B 
            WHERE INS_ID IN 
                (SELECT INS_MAP_ID 
                 FROM T_IN_MAP 
                 where T_IN_MAP_ID = (SELECT T_ID 
                                      FROM TW 
                                      WHERE TNAM = 'abc')
                )
            ) 
    AND T_DATE between '2015-01-01' and '2015-07-01' 
    AND INS_NAME NOT LIKE 'x%pr%' 
    AND INS_NAME like 'x%y%z') t4

AM using MYSQL. Could you please amend the query to include the headers exactly as per the output I've requested? I'd truly appreciate any help — User12345, Jun 17 '15 at 07:13
Hope this link will help to you . http://stackoverflow.com/questions/16359345/transposing-dynamic-columns-to-rows — praveen_programmer, Jun 17 '15 at 07:16
Hello Praveen/All - I did try to follow the example there in Praveen's link. I can't get it working unfortunately :(. I replaced the "," between SELECT statements with UNION firstly & then UNION ALL. This didn't help. Then I merged t1,t2,t3,t4 as just t1 which didn't help either. Tried a couple other tricks following the example which hasn't helped. Can anyone please format my query for me to print that output. I'm quite new to writing fairly complex SQL queries & hence the request. — User12345, Jun 17 '15 at 07:31

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

I believe you'll need a Pivot to turn row data into columns. This topic is almost exactly what you need: MySQL pivot table

edited May 23 '17 at 12:29

Community

1
1

answered Jun 17 '15 at 07:35

Christopher Gibson

68
9

Thank you heaps for guiding me to the right track. Looking around for pivot stuff, I bumped on this article - MySql Transpose Row into Column and Column into Row. I've amended my SQL inline with this also incorporating guidelines from Christopher Gibson's comment. It's still not working. This looks pretty complex I'll now look at other alternates of handling this in the UI code. A special thanks to you Praveen & Christopher. Appreciate your help. – User12345 Jun 19 '15 at 04:22

SQL - format output

1 Answers1