Browsing Tag

CTE

SQL Blogsby Rudy1 min5,526

Display results Horizontally in SQL Server

We are all familiar with seeing our data in a vertical format from SSMS. Here is a sample:

SELECT  *
FROM    #EmployeeJobs
 
Employee_ID	Job_id
100	500
100	501
100	502
100	503
100	504
191	501
191	502
191	503
191	505
188	503
188	504

But what if you, or your customer want’s the data horizontally? That is easy enough with a CTE. Continue reading “Display results Horizontally in SQL Server”

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter

SQL Blogsby Rudy1 min1,761

Common Table Expressions (CTE) in SQL Server 2005 and Up

Thanks to the team at TechRepublic.com and BOL, I have used CTEs to delete duplicates with ease in SQL Server 2005 and higher. Learn more about CTEs  at TechRepublic. Here meat of the article a sample: ;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking) AS (SELECT Product , SaleDate , SalePrice , Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC) FROM SalesHistory ) DELETE FROM SalesCTE WHERE Ranking > 1 Very easy and handy! Check out BOL for all kinds of information regarding Dense_Rank(), NEW_ID(), and PARTITION BY. While you’re there, check out RANK(), NTILE(), and ROW_NUMBER() I’m sure … Continue reading Common Table Expressions (CTE) in SQL Server 2005 and Up