How to get below query in SQL server 2008

Question

Post reply

How to get below query in SQL server 2008

sunilkmr284

Old Hand

Points: 309
More actions
October 10, 2019 at 3:56 am
Go to Answer
#3687641

Dear All,
I have a table with one field name as City.
Below are the data.
City
--------
A
B
C
D
E
F
G
H
Write a query or stored procedure or function?
If a pass 2 , the query result will be below
A      B
C    D
E    F
G   H
If a pass 3, the query result will be below
A     B     C
D    E      F
G   H
If a pass 4, the query result will be below
A     B      C     D
E     F     G     H
If a pass 5, the query result will be and so on as above result.

Thanks in advance

Luis Cazares

SSC Guru

Points: 183706

October 10, 2019 at 1:42 pm

Answer

#3687731

So much code for a simple pivot.

--Create sample data
CREATE TABLE #Table 
(
City VARCHAR(50)
)

INSERT INTO #Table (City) 
VALUES ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H');


--Solution starts here. The first variable can be converted into a parameter when converting the query into a stored procedure.
DECLARE @Columns int = 5;

DECLARE @SQL    NVARCHAR(MAX),
        @Cols   NVARCHAR(MAX);

-- Create code for as many columns as necessary
WITH 
E(n) AS(
    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
cteTally(n) AS(
    SELECT TOP( @Columns) 
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
    FROM E a, E b
) 
SELECT @Cols = STUFF(( SELECT NCHAR(9) + REPLACE( ',MAX( CASE WHEN ColumnOrder = <<N>> THEN City END) AS Col<<N>>', N'<<N>>', N) + NCHAR(10)
                FROM cteTally
                FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)'), 2, 1, N' ');
        
--Add the columns code to the whole query
--ColumnOrder will assign the cities to each column
--ColumnGroup will assign the cities to independent rows
SET @SQL = N'
WITH CTE AS(
    SELECT *,
           (ROW_NUMBER() OVER( ORDER BY City) - 1) % @Columns + 1 ColumnOrder,
           (ROW_NUMBER() OVER( ORDER BY City) - 1) / @Columns + 1 ColumnGroup
    FROM #Table
)
SELECT 
' + @Cols 
+ N'FROM CTE
GROUP BY ColumnGroup;';

--This is for debugging purposes 
--PRINT @SQL;

EXECUTE sp_executesql @SQL, N'@Columns int', @Columns;


GO
--Clean my environment
DROP TABLE #Table;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Brahmanand Shukla SSC Eights! Points: 990 More actions · Answer 1

 /*
EXEC #usp_Get_City
@No_Of_Rows_To_Group = 1

EXEC #usp_Get_City
@No_Of_Rows_To_Group = 2

EXEC #usp_Get_City
@No_Of_Rows_To_Group = 3

EXEC #usp_Get_City
@No_Of_Rows_To_Group = 4

EXEC #usp_Get_City
@No_Of_Rows_To_Group = 5

EXEC #usp_Get_City
@No_Of_Rows_To_Group = 6
*/
CREATE PROCEDURE #usp_Get_City
(
@No_Of_Rows_To_Group TINYINT
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @Partition TINYINT

DECLARE @Table TABLE
(
City VARCHAR(50)
)

INSERT INTO @Table (City) VALUES ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H')

IF OBJECT_ID('tempdb..#city_stag') IS NOT NULL DROP TABLE #city_stag
SELECT ROW_NUMBER() OVER(ORDER BY City ASC) AS RowID
, 
CASE (ROW_NUMBER() OVER(ORDER BY City ASC) % NULLIF(@No_Of_Rows_To_Group, 0))
WHEN 0 THEN @No_Of_Rows_To_Group
ELSE (ROW_NUMBER() OVER(ORDER BY City ASC) % NULLIF(@No_Of_Rows_To_Group, 0))
END AS GroupID
, City
INTO #city_stag
FROM @Table

SELECT @Partition = COUNT(1)
FROM #city_stag
WHERE GroupID = 1

; WITH cte_groups_stag
AS
(
SELECT RowID
, NTILE(ISNULL(@Partition, 1)) OVER(PARTITION BY GroupID ORDER BY RowID ASC) AS GroupID
, City
FROM #city_stag
)
, cte_unique_groups_stag
AS
(
SELECT DISTINCT GroupID
FROM cte_groups_stag
)

SELECT GroupID
, City = STUFF( 
(
SELECT ',' + City
FROM cte_groups_stag STG1
WHERE STG1.GroupID = STG2.GroupID
FOR XML PATH('')
), 1, 1, '')
FROM cte_unique_groups_stag STG2
END

Output

If you want city in the different columns instead of comma separated values then you can use the dynamic sql.

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

sunilkmr284 Old Hand Points: 309 More actions · Answer 2

Hi,

Thank you so much

Output will be as Column as well

Write a query or stored procedure or function?

If a pass 2 , the query result will be below

Col1 Col2

A B

C D

E F

G H

If a pass 3, the query result will be below

Col1 Col2 Col3

A B C

D E F

G H

If a pass 4, the query result will be below

Col1 Col2 Col3 Clo4

A B C D

E F G H

Brahmanand Shukla SSC Eights! Points: 990 More actions · Answer 3

Here you go. Mark it as answer if it serves your purpose.

First of all create the below user defined scalar function

CREATE FUNCTION [ufn_SPLIT_STRING_BY_DELIMITER](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000),ID int)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
DECLARE @ID INT
SET @ID=1 
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items,ID) VALUES(@SLICE,@ID)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
set @ID=@ID+1
IF LEN(@STRING) = 0 BREAK
END
RETURN
END

Now try the below procedure

/*
EXEC #usp_Get_City
@No_Of_Rows_To_Group = 1

EXEC #usp_Get_City
@No_Of_Rows_To_Group = 2

EXEC #usp_Get_City
@No_Of_Rows_To_Group = 3

EXEC #usp_Get_City
@No_Of_Rows_To_Group = 4
*/
ALTER PROCEDURE #usp_Get_City
(
@No_Of_Rows_To_Group TINYINT
)
AS
BEGIN
SET NOCOUNT ON

IF @No_Of_Rows_To_Group > 4 
BEGIN
RAISERROR ('@No_Of_Rows_To_Group cannot be greater than 4. If you wish to have it then amend the code accordingly', 16, 1); 
RETURN;
END

DECLARE @Partition TINYINT

DECLARE @Table TABLE
(
City VARCHAR(50)
)

INSERT INTO @Table (City) VALUES ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H')

IF OBJECT_ID('tempdb..#Output') IS NOT NULL DROP TABLE #Output
CREATE TABLE #Output
(
GroupID INT
, City VARCHAR(MAX)
, Col1 VARCHAR(50)
, Col2 VARCHAR(50)
, Col3 VARCHAR(50)
, Col4 VARCHAR(50)
)

IF OBJECT_ID('tempdb..#city_stag') IS NOT NULL DROP TABLE #city_stag
SELECT ROW_NUMBER() OVER(ORDER BY City ASC) AS RowID
, 
CASE (ROW_NUMBER() OVER(ORDER BY City ASC) % NULLIF(@No_Of_Rows_To_Group, 0))
WHEN 0 THEN @No_Of_Rows_To_Group
ELSE (ROW_NUMBER() OVER(ORDER BY City ASC) % NULLIF(@No_Of_Rows_To_Group, 0))
END AS GroupID
, City
INTO #city_stag
FROM @Table

SELECT @Partition = COUNT(1)
FROM #city_stag
WHERE GroupID = 1

; WITH cte_groups_stag
AS
(
SELECT RowID
, NTILE(ISNULL(@Partition, 1)) OVER(PARTITION BY GroupID ORDER BY RowID ASC) AS GroupID
, City
FROM #city_stag
)
, cte_unique_groups_stag
AS
(
SELECT DISTINCT GroupID
FROM cte_groups_stag
)

INSERT INTO #Output (GroupID, City)
SELECT GroupID
, City = STUFF( 
(
SELECT ',' + City
FROM cte_groups_stag STG1
WHERE STG1.GroupID = STG2.GroupID
FOR XML PATH('')
), 1, 1, '')
FROM cte_unique_groups_stag STG2

UPDATE OUTP
SET OUTP.Col1 = STRNG.items
FROM #Output OUTP
CROSS APPLY ufn_SPLIT_STRING_BY_DELIMITER (OUTP.City, ',') STRNG
WHERE STRNG.ID = 1

UPDATE OUTP
SET OUTP.Col2 = STRNG.items
FROM #Output OUTP
CROSS APPLY ufn_SPLIT_STRING_BY_DELIMITER (OUTP.City, ',') STRNG
WHERE STRNG.ID = 2

UPDATE OUTP
SET OUTP.Col3 = STRNG.items
FROM #Output OUTP
CROSS APPLY ufn_SPLIT_STRING_BY_DELIMITER (OUTP.City, ',') STRNG
WHERE STRNG.ID = 3

UPDATE OUTP
SET OUTP.Col4 = STRNG.items
FROM #Output OUTP
CROSS APPLY ufn_SPLIT_STRING_BY_DELIMITER (OUTP.City, ',') STRNG
WHERE STRNG.ID = 4

DECLARE @Col_Number TINYINT
, @Col_Name NVARCHAR(MAX)

SET @Col_Number = 1
WHILE @Col_Number <= @No_Of_Rows_To_Group
BEGIN
IF @Col_Number = 1 SET @Col_Name = '[Col' + CAST(@Col_Number AS VARCHAR) + ']'
ELSE SET @Col_Name = @Col_Name + ', ' + '[Col' + CAST(@Col_Number AS VARCHAR) + ']'

SET @Col_Number = @Col_Number + 1;
END

EXECUTE ('SELECT ' + @Col_Name + ' FROM #Output')

IF OBJECT_ID('tempdb..#Output') IS NOT NULL DROP TABLE #Output
END