How to convert Rows from multiple tables into Columns with values in Sql Server 2005 RSS

• • Reply
  

  sandeepmitta...

  Contributor

  6779 Points

  1058 Posts

  Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

  Mar 02, 2012 10:39 AM|LINK

  The problem is not BCE column, but the problem is that how would you link BCE data with user for display as required.

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
• • Reply
  

  Sukhjeevan S...

  Member

  38 Points

  51 Posts

  Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

  Mar 12, 2012 04:25 AM|LINK

  Data should display as given below :

  

• • Reply
  

  sivaram.yv_8...

  Member

  2 Points

  1 Post

  Re: How to convert Rows from multiple tables into Columns with values in Sql Server 2005

  Apr 03, 2012 01:02 PM|LINK

  There is a way to do it without using pivot.

  CREATE TABLE TABLE1 (USERID NUMBER, USERNAME VARCHAR(10), DEPARTMENT VARCHAR(10));
  CREATE TABLE TABLE2 (UNIQUEID NUMBER, USERID NUMBER, LANGUAGE VARCHAR(20));
  CREATE TABLE TABLE3 (UNIQUEID NUMBER, USERID NUMBER, COUNTRYPASSPORT VARCHAR(20));

  INSERT INTO TABLE1
  SELECT 1, 'AA', 'TT' FROM DUAL
  UNION ALL SELECT 2, 'BB', 'GG' FROM DUAL
  UNION ALL SELECT 3, 'CC', 'RR' FROM DUAL
  UNION ALL SELECT 4, 'DD', 'DD' FROM DUAL;

  
  INSERT INTO TABLE2
  SELECT 1,1,'AMERICAN' FROM DUAL
  UNION ALL SELECT 1,1,'ARABIC' FROM DUAL
  UNION ALL SELECT 1,2,'AZERBAIJANI' FROM DUAL
  UNION ALL SELECT 1,3,'BULGARIAN' FROM DUAL
  UNION ALL SELECT 1,3,'CHAOCHOW' FROM DUAL
  UNION ALL SELECT 1,4,'BEHDINI' FROM DUAL;

  
  INSERT INTO TABLE3
  SELECT 1,1,'HONKONG' FROM DUAL
  UNION ALL SELECT 1,1,'MALAYSIA' FROM DUAL
  UNION ALL SELECT 1,2,'SPAIN' FROM DUAL
  UNION ALL SELECT 1,3,'CHINA' FROM DUAL
  UNION ALL SELECT 1,3,'INDIA' FROM DUAL
  UNION ALL SELECT 1,4,'UK' FROM DUAL;

  CREATE TABLE TARGET_TABLE AS
  SELECT USERNAME,
         DEPARTMENT,
         (CASE
           WHEN AMERICAN = '1' THEN
            'YES'
           ELSE
            'NO'
         END) AMERICAN,
         (CASE
           WHEN ARABIC = '1' THEN
            'YES'
           ELSE
            'NO'
         END) ARABIC,
         (CASE
           WHEN AZERBAIJANI = '1' THEN
            'YES'
           ELSE
            'NO'
         END) AZERBAIJANI,
         (CASE
           WHEN BULGARIAN = '1' THEN
            'YES'
           ELSE
            'NO'
         END) BULGARIAN,
         (CASE
           WHEN CHAOCHOW = '1' THEN
            'YES'
           ELSE
            'NO'
         END) CHAOCHOW,
         (CASE
           WHEN BEHDINI = '1' THEN
            'YES'
           ELSE
            'NO'
         END) BEHDINI,
         (CASE
           WHEN HONKONG = '1' THEN
            'YES'
           ELSE
            'NO'
         END) HONKONG,
         (CASE
           WHEN MALAYSIA = '1' THEN
            'YES'
           ELSE
            'NO'
         END) MALAYSIA,
         (CASE
           WHEN SPAIN = '1' THEN
            'YES'
           ELSE
            'NO'
         END) SPAIN,
         (CASE
           WHEN INDIA = '1' THEN
            'YES'
           ELSE
            'NO'
         END) INDIA,
         (CASE
           WHEN CHINA = '1' THEN
            'YES'
           ELSE
            'NO'
         END) CHINA,
         (CASE
           WHEN UK = '1' THEN
            'YES'
           ELSE
            'NO'
         END) UK
    FROM (SELECT USERNAME,
                 DEPARTMENT,
                 SUM(CASE
                       WHEN STATUS = 'AMERICAN' THEN
                        '1'
                       ELSE
                        '0'
                     END) AMERICAN,
                 SUM(CASE
                       WHEN STATUS = 'ARABIC' THEN
                        '1'
                       ELSE
                        '0'
                     END) ARABIC,
                 SUM(CASE
                       WHEN STATUS = 'AZERBAIJANI' THEN
                        '1'
                       ELSE
                        '0'
                     END) AZERBAIJANI,
                 SUM(CASE
                       WHEN STATUS = 'BULGARIAN' THEN
                        '1'
                       ELSE
                        '0'
                     END) BULGARIAN,
                 SUM(CASE
                       WHEN STATUS = 'CHAOCHOW' THEN
                        '1'
                       ELSE
                        '0'
                     END) CHAOCHOW,
                 SUM(CASE
                       WHEN STATUS = 'BEHDINI' THEN
                        '1'
                       ELSE
                        '0'
                     END) BEHDINI,
                 SUM(CASE
                       WHEN STATUS = 'HONKONG' THEN
                        '1'
                       ELSE
                        '0'
                     END) HONKONG,
                 SUM(CASE
                       WHEN STATUS = 'MALAYSIA' THEN
                        '1'
                       ELSE
                        '0'
                     END) MALAYSIA,
                 SUM(CASE
                       WHEN STATUS = 'SPAIN' THEN
                        '1'
                       ELSE
                        '0'
                     END) SPAIN,
                 SUM(CASE
                       WHEN STATUS = 'INDIA' THEN
                        '1'
                       ELSE
                        '0'
                     END) INDIA,
                 SUM(CASE
                       WHEN STATUS = 'CHINA' THEN
                        '1'
                       ELSE
                        '0'
                     END) CHINA,
                 SUM(CASE
                       WHEN STATUS = 'UK' THEN
                        '1'
                       ELSE
                        '0'
                     END) UK
            FROM (SELECT A.USERNAME, DEPARTMENT, STATUS
                    FROM TABLE1 A,
                         (SELECT B.USERID, B.USERNAME, LANGUAGE AS STATUS
                            FROM TABLE2 A, TABLE1 B
                           WHERE A.USERID = B.USERID
                          UNION ALL
                          SELECT B.USERID, B.USERNAME, COUNTRYPASSPORT
                            FROM TABLE3 A, TABLE1 B
                           WHERE A.USERID = B.USERID) B
                   WHERE A.USERID = B.USERID)
           GROUP BY USERNAME, DEPARTMENT);

   

  Thanks and Regards,

  Y.V. Sivaram