Help Needed in Complex Logic

  • Hi,

    My table and data:

    Create table Sample(Empid int primary key identity(1,1),EmpName varchar(50),Empsalary money,EmpDesignation varchar(30));

    insert into Sample values('Jhon',8000,'Manager'),

    ('Smith',6000,'Lead'),

    ('Samuel',4000,'AccountExecutive'),

    ('Simson',4000,'AccountSpecialist'),

    ('Eric',22000,'Director'),

    ('Jonathan',12000,'SeniorManager')

    Expected result:

    select 'EmpName','Jhon','Smith','Samuel','Simson','Eric','Jonathan' union all

    select 'Salary','8000','6000','4000','4000','22000','12000' union all

    select 'Designation','Manager','Lead','AccountExecutive','AccountSpecialist','Director','SeniorManager'

    Is it possible to do without using loop? can anyone please give me some sample query to achieve

    Thanks

  • Take a look at this article, Script to create dynamic PIVOT queries in SQL Server[/url]. It should have what you need.

    😎

  • Hi Erikkur,

    Thanks for the reply. Here is my try.

    SELECT *

    FROM

    (

    select Val,Cat,ROW_NUMBER() over (partition by Cat ORDER BY Empid ) AS Rn

    FROM (SELECT Empid,Empname,CAST(Empsalary as varchar(50)) AS EmpSalary,CAST(Empdesignation as varchar(50)) AS Empdesignation FROM Sample)s

    unpivot (Val FOR Cat IN ([EmpName],[EmpSalary],[EmpDesignation]))u

    )m

    PIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4],[5],[6]))p

    ORDER BY CASE Cat WHEN 'Empname' THEN 1 WHEN 'salary' THEN 2 ELSE 3 END

    But i have half million records in my table and if i need to select Top 10000, i need to make this as dyncamic. Any help on making it to dynamic sql please

  • KGJ-Dev (5/6/2014)


    But i have half million records in my table and if i need to select Top 10000, i need to make this as dyncamic. Any help on making it to dynamic sql please

    Does that mean that you will be outputting 10000 columns or more?

    😎

  • Yes You are Correct. Could you please help me on making this as Dynamic to achieve my output.

    Thanks

  • You may want to look at this first, Maximum Capacity Specifications for SQL Server, and maybe rethink the approach.

    😎

  • thank you and am aware of this limitation. Is it possible to give me the dynamic query? i am planing to retrieve first 10000 rows for testing.

  • KGJ-Dev (5/6/2014)


    thank you and am aware of this limitation. Is it possible to give me the dynamic query? i am planing to retrieve first 10000 rows for testing.

    10,000 rows would transpose to 10,001 columns if it didn't throw an error. You may benefit from revisiting those limitations.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi Chris,

    Thanks for your reply.

    could you please help me on making the below query to fetch top 5000 records. currently it only take 6 records.

    SELECT *

    FROM

    (

    select Val,Cat,ROW_NUMBER() over (partition by Cat ORDER BY Empid ) AS Rn

    FROM (SELECT Empid,Empname,CAST(Empsalary as varchar(50)) AS EmpSalary,CAST(Empdesignation as varchar(50)) AS Empdesignation FROM Sample)s

    unpivot (Val FOR Cat IN ([EmpName],[EmpSalary],[EmpDesignation]))u

    )m

    PIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4],[5],[6]))p

    ORDER BY CASE Cat WHEN 'Empname' THEN 1 WHEN 'salary' THEN 2 ELSE 3 END

    Help me on making this be dynamic sql

    Thanks

  • KGJ-Dev (5/6/2014)


    Hi Chris,

    Thanks for your reply.

    could you please help me on making the below query to fetch top 5000 records. currently it only take 6 records.

    SELECT *

    FROM

    (

    select Val,Cat,ROW_NUMBER() over (partition by Cat ORDER BY Empid ) AS Rn

    FROM (SELECT Empid,Empname,CAST(Empsalary as varchar(50)) AS EmpSalary,CAST(Empdesignation as varchar(50)) AS Empdesignation FROM Sample)s

    unpivot (Val FOR Cat IN ([EmpName],[EmpSalary],[EmpDesignation]))u

    )m

    PIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4],[5],[6]))p

    ORDER BY CASE Cat WHEN 'Empname' THEN 1 WHEN 'salary' THEN 2 ELSE 3 END

    Help me on making this be dynamic sql

    Thanks

    Are you sure you have read the article posted about limitations?

    Columns per SELECT statement = 4,096

    What is the purpose of what you are doing? This isn't usable in this format by anything other than a computer which should be able to handle the data in a standard format anyway.

    If you are deadset on trying to force this you will have to first reduce the number of columns. Then you will need to use some dynamic sql. This is a twist on a dynamic cross tab. Take a look at the articles in my signature. They will help you get started.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • ChrisM@Work (5/6/2014)


    KGJ-Dev (5/6/2014)


    thank you and am aware of this limitation. Is it possible to give me the dynamic query? i am planing to retrieve first 10000 rows for testing.

    10,000 rows would transpose to 10,001 columns if it didn't throw an error. You may benefit from revisiting those limitations.

    Sure. You haven't read the document which Eirikur linked or you wouldn't have asked for this - but here it is. Note the limiter.

    DECLARE @Statement VARCHAR(MAX)

    SET @Statement = '

    SELECT *

    FROM

    (

    SELECT

    Val,

    Cat,

    Rn = ROW_NUMBER() OVER (PARTITION BY Cat ORDER BY Empid )

    FROM (

    SELECT

    Empid,

    Empname,

    EmpSalary = CAST(Empsalary as varchar(50)),

    Empdesignation = CAST(Empdesignation as varchar(50))

    FROM #Sample

    )s

    unpivot (

    Val FOR Cat IN (EmpName, EmpSalary, EmpDesignation)

    )u

    )m

    PIVOT(

    MAX(Val) FOR Rn IN (' + STUFF(

    (SELECT TOP (10)

    [text()] = ',[' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(MAX)) + ']'

    FROM

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)

    FOR XML PATH(''))

    ,1,1,'') + ')

    )p

    '

    PRINT @Statement

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • PIVOT / UNPIVOT haven't been told about the output column limitation of SELECT. Here's how to get 10,001 output columns from a SELECT:

    DROP table #Sample

    Create table #Sample (Empid int primary key identity(1,1),EmpName varchar(50),Empsalary money,EmpDesignation varchar(30));

    insert into #Sample values('Jhon1',8000,'Manager'),

    ('Smith2',6000,'Lead'),

    ('Samuel3',4000,'AccountExecutive'),

    ('Simson4',4000,'AccountSpecialist'),

    ('Eric5',22000,'Director'),

    ('Jonathan6',12000,'SeniorManager') -- 6 rows

    INSERT INTO #Sample (EmpName,Empsalary,EmpDesignation)

    SELECT EmpName = a.EmpName + CAST(ROW_NUMBER() OVER(ORDER BY f.Empid, e.Empid, d.Empid, c.Empid, b.Empid, a.empid) AS VARCHAR(5)), a.Empsalary, a.EmpDesignation

    FROM #Sample a, #Sample b, #Sample c, #Sample d, #Sample e, #Sample f

    -- 46662 rows

    DECLARE @Statement VARCHAR(MAX)

    SET @Statement = '

    SELECT *

    FROM

    (

    SELECT

    Val,

    Cat,

    Rn = ROW_NUMBER() OVER (PARTITION BY Cat ORDER BY Empid )

    FROM (

    SELECT

    Empid,

    Empname,

    EmpSalary = CAST(Empsalary as varchar(50)),

    Empdesignation = CAST(Empdesignation as varchar(50))

    FROM #Sample

    )s

    unpivot (

    Val FOR Cat IN (EmpName, EmpSalary, EmpDesignation)

    )u

    )m

    PIVOT(

    MAX(Val) FOR Rn IN (' + STUFF(

    (SELECT TOP (10000)

    [text()] = ',[' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(MAX)) + ']'

    FROM

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)

    FOR XML PATH(''))

    ,1,1,'') + ')

    )p

    '

    PRINT @Statement

    EXEC(@Statement)

    -- 500 employees / 00:05

    -- 1000 employees / 00:10

    -- 2000 employees / 00:21

    -- 4000 employees / 00:45

    -- 8000 employees / 01:43

    -- 10000 employees / 02:22

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (5/6/2014)


    PIVOT / UNPIVOT haven't been told about the output column limitation of SELECT. Here's how to get 10,001 output columns from a SELECT:

    DROP table #Sample

    Create table #Sample (Empid int primary key identity(1,1),EmpName varchar(50),Empsalary money,EmpDesignation varchar(30));

    insert into #Sample values('Jhon1',8000,'Manager'),

    ('Smith2',6000,'Lead'),

    ('Samuel3',4000,'AccountExecutive'),

    ('Simson4',4000,'AccountSpecialist'),

    ('Eric5',22000,'Director'),

    ('Jonathan6',12000,'SeniorManager') -- 6 rows

    INSERT INTO #Sample (EmpName,Empsalary,EmpDesignation)

    SELECT EmpName = a.EmpName + CAST(ROW_NUMBER() OVER(ORDER BY f.Empid, e.Empid, d.Empid, c.Empid, b.Empid, a.empid) AS VARCHAR(5)), a.Empsalary, a.EmpDesignation

    FROM #Sample a, #Sample b, #Sample c, #Sample d, #Sample e, #Sample f

    -- 46662 rows

    DECLARE @Statement VARCHAR(MAX)

    SET @Statement = '

    SELECT *

    FROM

    (

    SELECT

    Val,

    Cat,

    Rn = ROW_NUMBER() OVER (PARTITION BY Cat ORDER BY Empid )

    FROM (

    SELECT

    Empid,

    Empname,

    EmpSalary = CAST(Empsalary as varchar(50)),

    Empdesignation = CAST(Empdesignation as varchar(50))

    FROM #Sample

    )s

    unpivot (

    Val FOR Cat IN (EmpName, EmpSalary, EmpDesignation)

    )u

    )m

    PIVOT(

    MAX(Val) FOR Rn IN (' + STUFF(

    (SELECT TOP (10000)

    [text()] = ',[' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(MAX)) + ']'

    FROM

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)

    FOR XML PATH(''))

    ,1,1,'') + ')

    )p

    '

    PRINT @Statement

    EXEC(@Statement)

    -- 500 employees / 00:05

    -- 1000 employees / 00:10

    -- 2000 employees / 00:21

    -- 4000 employees / 00:45

    -- 8000 employees / 01:43

    -- 10000 employees / 02:22

    Interesting. I have never wanted or bothered to see if I could get more than 10k columns. I still wonder what the real world application of something like this is.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (5/6/2014)


    ChrisM@Work (5/6/2014)


    PIVOT / UNPIVOT haven't been told about the output column limitation of SELECT. Here's how to get 10,001 output columns from a SELECT:

    DROP table #Sample

    Create table #Sample (Empid int primary key identity(1,1),EmpName varchar(50),Empsalary money,EmpDesignation varchar(30));

    insert into #Sample values('Jhon1',8000,'Manager'),

    ('Smith2',6000,'Lead'),

    ('Samuel3',4000,'AccountExecutive'),

    ('Simson4',4000,'AccountSpecialist'),

    ('Eric5',22000,'Director'),

    ('Jonathan6',12000,'SeniorManager') -- 6 rows

    INSERT INTO #Sample (EmpName,Empsalary,EmpDesignation)

    SELECT EmpName = a.EmpName + CAST(ROW_NUMBER() OVER(ORDER BY f.Empid, e.Empid, d.Empid, c.Empid, b.Empid, a.empid) AS VARCHAR(5)), a.Empsalary, a.EmpDesignation

    FROM #Sample a, #Sample b, #Sample c, #Sample d, #Sample e, #Sample f

    -- 46662 rows

    DECLARE @Statement VARCHAR(MAX)

    SET @Statement = '

    SELECT *

    FROM

    (

    SELECT

    Val,

    Cat,

    Rn = ROW_NUMBER() OVER (PARTITION BY Cat ORDER BY Empid )

    FROM (

    SELECT

    Empid,

    Empname,

    EmpSalary = CAST(Empsalary as varchar(50)),

    Empdesignation = CAST(Empdesignation as varchar(50))

    FROM #Sample

    )s

    unpivot (

    Val FOR Cat IN (EmpName, EmpSalary, EmpDesignation)

    )u

    )m

    PIVOT(

    MAX(Val) FOR Rn IN (' + STUFF(

    (SELECT TOP (10000)

    [text()] = ',[' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(MAX)) + ']'

    FROM

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)

    FOR XML PATH(''))

    ,1,1,'') + ')

    )p

    '

    PRINT @Statement

    EXEC(@Statement)

    -- 500 employees / 00:05

    -- 1000 employees / 00:10

    -- 2000 employees / 00:21

    -- 4000 employees / 00:45

    -- 8000 employees / 01:43

    -- 10000 employees / 02:22

    Interesting. I have never wanted or bothered to see if I could get more than 10k columns. I still wonder what the real world application of something like this is.

    Me neither. I was expecting an error at 2000 employees, but it just kept going. I wouldn't trust the result set.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (5/6/2014)


    PIVOT / UNPIVOT haven't been told about the output column limitation of SELECT

    Out of curiosity, which server version and which SSMS version?

    BTW: nice! 😎

Viewing 15 posts - 1 through 15 (of 28 total)

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