Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help Needed in Complex Logic


Help Needed in Complex Logic

Author
Message
KGJ-Dev
KGJ-Dev
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 845
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
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6844 Visits: 17777
Take a look at this article, Script to create dynamic PIVOT queries in SQL Server. It should have what you need.
Cool
KGJ-Dev
KGJ-Dev
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 845
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
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6844 Visits: 17777
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?
Cool
KGJ-Dev
KGJ-Dev
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 845
Yes You are Correct. Could you please help me on making this as Dynamic to achieve my output.

Thanks
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6844 Visits: 17777
You may want to look at this first, Maximum Capacity Specifications for SQL Server, and maybe rethink the approach.
Cool
KGJ-Dev
KGJ-Dev
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 845
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.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9028 Visits: 19041
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
Exploring Recursive CTEs by Example Dwain Camps
KGJ-Dev
KGJ-Dev
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 845
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16680 Visits: 17038
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search