[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

Rows to column

Hello,

Below is the query which currently returns data as shown below:

Current Format:                  
WFC	                        559403	0	  204
WFC	                        559403	1012	  0
XXX	                        1000	1	  0
XXX	                        1000	0	  4

Open in new window

How should I modify this query to get the data in the format as shown below?
WFC	                        559403	1012	     204
XXX	                        1000	1	     4

Open in new window


SELECT  c.company_code,
            c.user_id,
            TO_CHAR(NVL(v_Count_700,0))                         v_Count_700,
            TO_CHAR(NVL(v_Count_704,0) +  NVL(v_Count_705,0))   v_Cosolidated_Count_1
    FROM    (SELECT  b.company_code,
                     b.user_id,
                     b.type,
                     CASE WHEN type = '700' THEN COUNT(*) END v_Count_700,
                     CASE WHEN type = '704' THEN COUNT(*) END v_Count_704,
                     CASE WHEN type = '705' THEN COUNT(*) END v_Count_705                      
               FROM  (SELECT a.company_code,
                             a.user_id,
                             a.type
                        FROM a_log a ,
                             type_mvw t
                       WHERE a.type = t.type
                         AND t.type IN ('700','704','705')
                      ) b
             GROUP BY b.company_code,
                      b.user_id,
                      b.type
            )c;

Thanks!

0
sath350163
Asked:
sath350163
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Add a max.  Pasted on mobile.  Might have typos.

SELECT  c.company_code,
            c.user_id,
            max(TO_CHAR(NVL(v_Count_700,0)))                         v_Count_700,
            max(TO_CHAR(NVL(v_Count_704,0) +  NVL(v_Count_705,0)))   v_Cosolidated_Count_1

...
Group by company_code, user_id
0
 
sath350163Author Commented:
Thanks slightwv.
Your solution worked perfectly.
0
 
sath350163Author Commented:
Solution to convert individual rows to individual columns on the same row
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now