Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Help Needed in Complex Logic Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 6:54 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 7:39 AM
Points: 78, Visits: 264
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
Post #1567748
Posted Monday, May 5, 2014 10:39 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 1,908, Visits: 5,250
Take a look at this article, Script to create dynamic PIVOT queries in SQL Server. It should have what you need.
Post #1567769
Posted Tuesday, May 6, 2014 5:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 7:39 AM
Points: 78, Visits: 264
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
Post #1567882
Posted Tuesday, May 6, 2014 6:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 1,908, Visits: 5,250
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?
Post #1567889
Posted Tuesday, May 6, 2014 6:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 7:39 AM
Points: 78, Visits: 264
Yes You are Correct. Could you please help me on making this as Dynamic to achieve my output.

Thanks
Post #1567893
Posted Tuesday, May 6, 2014 6:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 1,908, Visits: 5,250
You may want to look at this first, Maximum Capacity Specifications for SQL Server, and maybe rethink the approach.
Post #1567897
Posted Tuesday, May 6, 2014 6:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 7:39 AM
Points: 78, Visits: 264
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.
Post #1567901
Posted Tuesday, May 6, 2014 6:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 6,719, Visits: 13,827
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
Post #1567906
Posted Tuesday, May 6, 2014 7:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 7:39 AM
Points: 78, Visits: 264
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
Post #1567942
Posted Tuesday, May 6, 2014 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:34 PM
Points: 12,923, Visits: 12,342
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)
Post #1567963
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse