How to partially transpose a table-CodePudding

I have to create a table with a lists of contacts (ClientCode, Telephone, Name) from a table structured like this:

ClientCode	Telephone1	Name1	Telephone2	Name2	Telephone3	Name3
1234	55555	John M.	79879	Frank	897987	Paul
9884	84416	Richard	88416	Helen	11594	Katrin

I need to group by ClientCode same persons that work at the same client.

Table expected:

ClientCode	Telephone	Name
1234	55555	John M.
1234	79879	Frank
1234	897987	Paul
9884	84416	Richard
9884	88416	Helen
9884	1159	Katrin

I've tried the following solution (from this answer) but the output is not correct

SELECT UNPVTBL.CLIENTCODE, UNPVTBL.NAME
FROM (SELECT * FROM -ORIGIN_TABLE-) P
UNPIVOT
(NAME FOR CONTACTS IN
    (NAME1, NAME2, NAME3)
)UNPVTBL

UNION
SELECT UNPVTBL.CLIENTCODE, UNPVTBL.TELEPHONE
FROM (SELECT * FROM -ORIGIN_TABLE-) G
UNPIVOT
(TELEPHONE FOR TELEPH IN
(TELEPHONE1, TELEPHONE2, TELEPHONE3) 
)UNPVTBL

CodePudding user response：

You can try doing it using the UNION ALL operator:

SELECT ClientCode, Telephone1 AS Telephone, Name1 AS Name FROM tab
UNION ALL
SELECT ClientCode, Telephone2 AS Telephone, Name2 AS Name FROM tab
UNION ALL
SELECT ClientCode, Telephone3 AS Telephone, Name3 AS Name FROM tab

Output:

ClientCode	Telephone	Name
1234	55555	John M.
9884	84416	Richard
1234	79879	Frank
9884	88416	Helen
1234	897987	Paul
9884	11594	Katrin

Check the demo here.

CodePudding user response：

A way to do this is VALUES unpivot:

select t.ClientCode,x.name, x.Telephone
from (
    VALUES  (1234, 55555, N'John M.', 79879, N'Frank', 897987, N'Paul')
    ,   (9884, 84416, N'Richard', 88416, N'Helen', 11594, N'Katrin')
) t (ClientCode,Telephone1,Name1,Telephone2,Name2,Telephone3,Name3)
cross apply (
    VALUES  (Telephone1, Name1)
    ,   (Telephone2, Name2)
    ,   (Telephone3, Name3)
    ) x (Telephone, Name)

CodePudding user response：

Hopefully this exercise is to fix you design; if it isn't, it should be.

As for the solution, a VALUES table construct seems to do this simply enough:

SELECT YT.ClientCode,
       V.Telephone,
       V.[Name]
FROM dbo.YourTable YT
     CROSS APPLY(VALUES(Telephone1,Name1),
                       (Telephone2,Name2),
                       (Telephone3,Name3))V(Telephone,Name);