How to get below query in SQL server 2008

  • sunilkmr284

    SSC Veteran

    Points: 247

    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

  • Brahmanand Shukla

    SSC-Addicted

    Points: 407

     /*
    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.

  • sunilkmr284

    SSC Veteran

    Points: 247

    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-Addicted

    Points: 407

    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
  • Luis Cazares

    SSC Guru

    Points: 183564

    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
  • Brahmanand Shukla

    SSC-Addicted

    Points: 407

    That's indeed a different perspective to solve the same problem. Much appreciated !

  • sunilkmr284

    SSC Veteran

    Points: 247

    Thank you Brahmanand Shukla!

  • sunilkmr284

    SSC Veteran

    Points: 247

    Thank you Luis Cazares!

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

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