CODEWITHSUNDEEP
sqlsql-server
Aathira
Aathira

Reputation: 813

Insert rows from one table to columns of another table with specific pattern

I have following table

enter image description here

I need to transform above data to another table in following format

enter image description here

Also source table will have varying number of TypeOfContact, thus I need a target table with dynamically generated column names. I am literally clueless about how to write a sql query to achieve this

Upvotes: 0

Views: 925

Answers (2)

Jai
Jai

Reputation: 26

CREATE TABLE #temp 
  ( 
     requestid     INT, 
     primaryname1  VARCHAR(50), 
     primaryemail1 VARCHAR(50), 
     sitename1     VARCHAR(50), 
     siteemail1    VARCHAR(50) 
  ) 

SELECT * 
INTO   #row 
FROM   (SELECT m.*, 
               Row_number() 
                 OVER( 
                   partition BY requestid, typeofcontact 
                   ORDER BY typeofcontact) rn 
        FROM   <your_table> m) m 

DECLARE @max_row     INT, 
        @current_row VARCHAR(5) = '1', 
        @SQL         VARCHAR(max) = '', 
        @alter_SQL   VARCHAR(max) = ''; 

SELECT @max_row = Max(rn) 
FROM   #row; 

WHILE @current_row <= @max_row 
  BEGIN 
      IF @current_row <> '1' 
        BEGIN 
            SET @alter_SQL = 'ALTER TABLE #temp add primaryname' 
                             + @current_row + ' varchar(50), primaryemail' 
                             + @current_row + ' varchar(50), sitename' + @current_row 
                             + ' varchar(50), siteemail' + @current_row 
                             + ' varchar(50)' 

            EXEC(@alter_SQL) 
        END 

      SET @SQL = @SQL + ',MAX(CASE WHEN rn=' + @current_row 
                 + ' and TypeOfContact = ''Primary''  THEN ContactName END) "a' 
                 + @current_row + '" ,MAX(CASE WHEN rn=' 
                 + @current_row 
                 + ' and TypeOfContact = ''Primary''  THEN ContactEmail END)    "b' 
                 + @current_row + '" ,MAX(CASE WHEN rn=' 
                 + @current_row 
                 + ' and TypeOfContact = ''Site''  THEN ContactName END)    "c' 
                 + @current_row + '" ,MAX(CASE WHEN rn=' 
                 + @current_row 
                 + ' and TypeOfContact = ''Site''  THEN ContactEmail END)   "d' 
                 + @current_row + '"' 
      SET @current_row = Cast(( Cast(@current_row AS INT) + 1 ) AS INT) 
  END 

SET @SQL = 'insert into #temp SELECT requestid' + @SQL 
           + 'FROM #row group by RequestId'

EXEC(@SQL) 

SELECT * 
FROM   cust 

SELECT * 
FROM   #temp

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

You may use pivoting logic along with ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY RequestId, TypeOfContact
                                 ORDER BY ContactEmail) rn
    FROM yourTable
)

SELECT
    RequestId,
    MAX(CASE WHEN TypeOfContact = 'Site' AND rn = 1
             THEN ContactName) AS SiteName1,
    MAX(CASE WHEN TypeOfContact = 'Site' AND rn = 1
             THEN ContactEmail) AS SiteEmail1,
    MAX(CASE WHEN TypeOfContact = 'Site' AND rn = 2
             THEN ContactName) AS SiteName2,
    MAX(CASE WHEN TypeOfContact = 'Site' AND rn = 2
             THEN ContactEmail) AS SiteEmail2,
    MAX(CASE WHEN TypeOfContact = 'Site' AND rn = 3
             THEN ContactName) AS SiteName3,
    MAX(CASE WHEN TypeOfContact = 'Site' AND rn = 3
             THEN ContactEmail) AS SiteEmail3,
    MAX(CASE WHEN TypeOfContact = 'Primary' AND rn = 1
             THEN ContactName) AS PrimaryName1,
    MAX(CASE WHEN TypeOfContact = 'Primary' AND rn = 1
             THEN ContactEmail) AS PrimaryEmail1,
    MAX(CASE WHEN TypeOfContact = 'Primary' AND rn = 2
             THEN ContactName) AS PrimaryName2,
    MAX(CASE WHEN TypeOfContact = 'Primary' AND rn = 2
             THEN ContactEmail) AS PrimaryEmail2,
    MAX(CASE WHEN TypeOfContact = 'Primary' AND rn = 3
             THEN ContactName) AS PrimaryName3,
    MAX(CASE WHEN TypeOfContact = 'Primary' AND rn = 3
             THEN ContactEmail) AS PrimaryEmail3
FROM cte
GROUP BY
    RequestId;

Upvotes: 2

Related Questions