I have some data that I'm trying to organise from an older system. It lists our emails and numbers on seperate rows within the database.

CREATE TABLE #temptable ( [LookupCode] char(10), [Title] varchar(20), [FirstName] varchar(30), [MiddleName] varchar(16), [LastName] varchar(30), [Number] varchar(150), [EmailWeb] varchar(150), [TypeCode] char(3), [Primary] int )
INSERT INTO #temptable ([LookupCode], [Title], [FirstName], [MiddleName], [LastName], [Number], [EmailWeb], [TypeCode], [Primary])
VALUES
( 'ANDERSSO01', 'Miss', 'Jessie', '', 'Bloggs', '', '[email protected]', 'EM1', 1 ), 
( 'ANDERSSO01', 'Mr', 'Joe', '', 'Bloggs', '01363936541', '', 'RES', 0 ), 
( 'ANDERSSO01', 'Mr', 'Joe', '', 'Bloggs', '', '[email protected]', 'EM1', 0 ), 
( 'ANDERSSO01', 'Miss', 'Jessie', '', 'Bloggs', '073321663', '', 'RES', 1 )

SELECT * FROM #temptable t

DROP TABLE #temptable

This basically returns the data as the following SQL Fiddle shows:

I have managed to get a result by initially doing subqueries - however these fall over for this example because there are multiple contacts for the same LookupCode, the subqueries only worked for one record.

Ultimately there should only be two rows - one for Jessie and one for Joe with both the number and email on the same line.

WHERE TypeCode = 'EM1'

is for an email and RES is for a Home tel - I have removed the ACT TypeCode as its erroneous.

Assistance would be appreciated I've tried using a PIVOT but with mixed results - an UNPIVOT doesn't work for the data is already in an UNPIVOT format essentially based on what TypeCode is filtered on.

**UPDATE - PIVOT EXAMPLE **

WITH
    cte
AS  (
        SELECT
                c.LookupCode
            , cn.LkPrefix 'Title'
            , cn.FirstName
            , cn.MiddleName
            , cn.LastName
              , CAST(cn2.Number AS VARCHAR(150)) 'Number'
              , CAST(cn2.EmailWeb AS VARCHAR(150)) 'EmailWeb'
              , cn2.TypeCode
              , cn2.TypeCode 'TypeCode2'
              , c.UniqEntity
              , cn2.UniqContactName
              , IIF(c.UniqContactNamePrimary = cn2.UniqContactName, 1, 0) "Primary"
        FROM
                dbo.Client        c
            LEFT OUTER JOIN
                dbo.ContactNumber cn2           
                    ON cn2.UniqEntity = c.UniqEntity --AND cn2.UniqContactName = c.UniqContactNamePrimary
            LEFT OUTER JOIN dbo.ContactName cn ON c.UniqEntity = cn.UniqEntity AND cn2.UniqContactName = cn.UniqContactName
        WHERE
                c.LookupCode = @LookupCode
    )
    select RES 'Home', MOB 'Mobile',  EM1 'Email', [Primary], FirstName, LastName
from
(
  select *
  from cte
) d
pivot
(
  max(Number)
  for TypeCode in (RES, MOB)
) piv
pivot
(
  max(EmailWeb)
  for TypeCode2 in (EM1)
) piv2

Result

CodePudding user response：

I would, personally, use conditional aggregation here. You don't explain what you want to do with the TypeCode and Primary values so i have omitted them, however, if you do want them in the query they will need to be aggregated in some way, not added to the GROUP BY (adding them to the GROUP BY will cause multiple rows):

SELECT t.LookupCode,
       t.Title,
       t.FirstName,
       t.LastName,
       MAX(t.Number) AS Number,
       MAX(t.EmailWeb) AS EmailWeb
FROM #temptable t
GROUP BY t.LookupCode,
         t.Title,
         t.FirstName,
         t.LastName;

CodePudding user response：

I gave this a try, maybe I have missed something but I did not see the need to PIVOT

    with data (LookupCode, Title, FirstName, MiddleName, LastName, Num, EmailWeb, 
    
        TypeCode, Primary)as (
        select 

 'ANDERSSO01', 'Miss', 'Jessie', '', 'Bloggs', '', '[email protected]', 'EM1', 1 from dual union all 
    select  'ANDERSSO01', 'Mr', 'Joe', '', 'Bloggs', '01363936541', '', 'RES', 0 from dual union all
    select  'ANDERSSO01', 'Mr', 'Joe', '', 'Bloggs', '', '[email protected]', 'EM1', 0 from dual union all
    select  'ANDERSSO01', 'Miss', 'Jessie', '', 'Bloggs', '073321663', '', 'RES', 1 from dual 
    )
    select d1.LookupCode
    , d1.Title
    , d1.FirstName
    , d1.MiddleName
    , d1.LastName
    , (select Num from data d2 where d1.LookupCode = d2.LookupCode
    and d1.Title = d2.Title
    and d1.FirstName = d2.FirstName
    and d2.TypeCode != 'EM1') num2
    , d1.EmailWeb
    , d1.TypeCode
    , d1.Primary 
    from data d1
    where d1.TypeCode = 'EM1'