re - arrange table

Question

I have a table like this:

ID    Country       InternetTLD       CallingCode
1     Nicaragua      .ni                +505
2     USA            .us                +1
3     Spain          .es                +34
4     Germany        .de                +49

and I need a result like this

  1           2         3         4
Nicaragua     USA      Spain     Germany
  .ni         .us       .es        .de
 +505          +1        +34       +49

I have tried with pivot but I just get to convert one column row, but in this case for every row in the first table it should be a column in the resulting table.

this is my code:

Create table #SampleTable (
ID int,
Country nvarchar(50),
InternetTLD nvarchar(50),
CallingCode nvarchar(50)
);

insert into #SampleTable (ID, Country, InternetTLD, CallingCode)
values
(1, 'Nicaragua', '.ni', '+505'),
(2, 'USA', '.us', '+505'),
(3, 'Spain', '.es', '+34'),
(4, 'Germany', '.de', '+49')




DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
declare @PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(ID)
FROM (SELECT ID FROM #SampleTable ) AS ID

--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
    = ISNULL(@PivotSelectColumnNames + ',','')
    + 'ISNULL(' + QUOTENAME(ID) + ', 0) AS '
    + QUOTENAME(ID)
FROM (SELECT ID FROM #SampleTable ) AS ID

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT  ' + @PivotSelectColumnNames + '
    FROM #SampleTable 
    PIVOT(MAX(Country) 
          FOR ID IN (' + @ColumnName + ')) AS PVTTable '

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

DROP TABLE #SampleTable

You can't have different data types in the same column. What are you really attempting to do? — KeithL, Oct 23 '18 at 14:41
@TabAlleman. . . This is not only `pivot`, it is both `pivot` & `unpivot`. — Yogesh Sharma, Oct 23 '18 at 14:48
. . . Since you are the new user, might be you don't know the rules of this site, so i recommend to take a small tour of this site. — Yogesh Sharma, Oct 23 '18 at 14:52
@KeithL this is a sample table, the real One has a list of branch offices with general info like telephone number, address, email, etc. What I want to do is use that info as a footer in a report that's why I want to reorder the table, thank you for your time. — Alvaro Alvarado, Oct 23 '18 at 16:01

Yogesh Sharma · Answer 1 · 2018-10-23T15:08:23.250

2

You need pivot & unpivot both so, i would do it with conditional aggregation & apply:

select max(case when id = 1 then val end) as [1], 
       max(case when id = 2 then val end) as [2], 
       max(case when id = 3 then val end) as [3],
       max(case when id = 4 then val end) as [4]
from table t cross apply
     ( values ('Country', Country, 1), ('InternetTLD', InternetTLD, 2), ('CallingCode', CallingCode, 3)  
     ) tt(col, val)
group by col, seq
order by seq;

edited Oct 23 '18 at 15:08

answered Oct 23 '18 at 14:50

Yogesh Sharma

49,870
5
26
52

Although this doesn't include the data in the order OP posted. If that's a requirement, you could add a "order by" column to your `VALUES`, and then order by it: `VALUES ('Country', Country, 1), ('InternetTLD', InternetTLD, 2)...` – Zack Oct 23 '18 at 15:05
the point is that te source table could have more than 4 rows, and second I need to exclude some of them under certain circumtances, so I need something more dynamic... – Alvaro Alvarado Oct 23 '18 at 15:35

re - arrange table

1 Answers1