Convert column into rows without using pivot

Introduction

We use pivot queries when we need to transform data from row-level to columnar data. But what would happen when someone ask to pivot table without using pivot function in sql. This scenario can be achieved by CTE (Common Table Expressions) and MAX case combination.

Background

This post intends to help T-SQL developers for solving interview question, and I am sure many of you must have came across this question several times in the past.

This approach becomes more helpful when we are working on other SQL workbench where there is no pivot function exist. such as Amazon redshift

Using the Code

Step 1: Create the test table. for this you can use scripts shown below

USE [Your Database Name]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[CountryDtls](

    [Id] [int] NOT NULL,

    [CountryName] [nvarchar](50) NULL,

    [CityName] [nvarchar](50) NULL,

 CONSTRAINT [PK_CountryDtls] PRIMARY KEY CLUSTERED 

(

    [Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

INSERT [dbo].[CountryDtls] ([Id], [CountryName], [CityName]) VALUES (1, N'India', N'Mumbai')

INSERT [dbo].[CountryDtls] ([Id], [CountryName], [CityName]) VALUES (2, N'India', N'Channai')

INSERT [dbo].[CountryDtls] ([Id], [CountryName], [CityName]) VALUES (3, N'India', N'Bhopal')

INSERT [dbo].[CountryDtls] ([Id], [CountryName], [CityName]) VALUES (4, N'India', N'Indore')

INSERT [dbo].[CountryDtls] ([Id], [CountryName], [CityName]) VALUES (5, N'Aus', N'Sedney')

INSERT [dbo].[CountryDtls] ([Id], [CountryName], [CityName]) VALUES (6, N'Aus', N'Melbourne')

INSERT [dbo].[CountryDtls] ([Id], [CountryName], [CityName]) VALUES (7, N'USA', N'NewYork')

INSERT [dbo].[CountryDtls] ([Id], [CountryName], [CityName]) VALUES (8, N'USA', N'Chicago')

INSERT [dbo].[CountryDtls] ([Id], [CountryName], [CityName]) VALUES (9, N'USA', N'Washington D C')

Step 2: After executing the script. your table will look like this:

Select * from CountryDtls

Step 3: Now find a column in your table which has common value. In our case column [countryName] has common values- see the image for this

Step 4: Once you have found the column name containing same value . Apply ROW_NUMBER() function over this column. in our case column [countryName] is containing same value so data will be partitioned by this column- see the image for this

Step 5 : Now after the partition we create CTE(Common Table Expressions) where CTE is kind of temporary table that can be used for writing recursive queries.

• Put our sub query inside the CTE
• Get the Max value of column [Row_no]. In our case it is 4, so we have to create 4 columns such as ([city_1] [city_2], [city_3], [city_4])

;with CTE_tbl as

(

SELECT CountryName,CityName,

ROW_NUMBER() OVER(PARTITION BY CountryName order by CountryName Desc) as row_no

FROM CountryDtls

)

select

   CountryName

,  Max(case when row_no=1 then cityName end )as City_1

,  Max(case when row_no=2 then cityName end )as City_2

,  Max(case when row_no=3 then cityName end )as City_3

,  Max(case when row_no=4 then cityName end )as City_4

FROM CTE_tbl

group By CountryName

Step 6: Finally see the output

Step 7: I am frequently being asked the question that what will happen when we add an extra row in country table?

For example: if we add another city name in country table then our query will not work properly. well this is relevant question but we have solution for this

• When we say city is added to the table and not to our CTE? It means table is dynamic so our CTE must be dynamic, to achieve this we will have to find the max value of column [row_no].
• We use table type variable (In our case @TatalRows is table type variable) to store all values of column [row_no] .
• Then get max value from @TatalRows(table type variable).
• Then we use while loop to create dynamic column name and store it into local variable @DynamicColumnName.
• Create dynamic query and place the dynamic column name(@DynamicColumnName) in right place

Below are the Dynamic query for pivoting data you can add extra row in country table and see the result:

 declare @maxColumnCount int=0;
 declare @Query varchar(max)='';
 declare @DynamicColumnName nvarchar(MAX)='';

-- table type variable that store all values of column row no
 DECLARE @TotalRows TABLE( row_count int)
 INSERT INTO @TotalRows (row_count)
 SELECT (ROW_NUMBER() OVER(PARTITION BY CountryName order by CountryName Desc)) as row_no FROM CountryDtls

-- Get the MAX value from @TotalRows table
 set @maxColumnCount= (select max(row_count) from @TotalRows)
 
-- loop to create Dynamic max/case and store it into local variable 
 DECLARE @cnt INT = 1;
 WHILE @cnt <= @maxColumnCount
 BEGIN
   set @DynamicColumnName= @DynamicColumnName + ', Max(case when row_no= '+cast(@cnt as varchar)+' then cityName end )as City_'+cast(@cnt as varchar)+''
   SET @cnt = @cnt + 1;
END;

-- Create dynamic CTE and store it into local variable @query 
  set @Query='
     with CTE_tbl as
     (
       SELECT CountryName,CityName,
       ROW_NUMBER() OVER(PARTITION BY CountryName order by CountryName Desc) as row_no
       FROM CountryDtls
      )
  select
     CountryName
     '+@DynamicColumnName+'
     FROM CTE_tbl
     group By CountryName'

-- Execute the Query
 execute (@Query)