May 5, 2014 at 6:54 pm
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
May 5, 2014 at 10:39 pm
May 6, 2014 at 5:42 am
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
May 6, 2014 at 6:01 am
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?
😎
May 6, 2014 at 6:07 am
Yes You are Correct. Could you please help me on making this as Dynamic to achieve my output.
Thanks
May 6, 2014 at 6:18 am
You may want to look at this first, Maximum Capacity Specifications for SQL Server, and maybe rethink the approach.
😎
May 6, 2014 at 6:37 am
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.
May 6, 2014 at 6:42 am
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.
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
May 6, 2014 at 7:36 am
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
May 6, 2014 at 7:58 am
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/
May 6, 2014 at 8:13 am
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
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
May 6, 2014 at 9:08 am
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
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
May 6, 2014 at 9:26 am
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 #SampleCreate 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/
May 6, 2014 at 9:32 am
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 #SampleCreate 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.
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
May 6, 2014 at 10:08 am
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