A CTE, DENSE_RANK (), String Concatenattion and PIVOT Sample


 

A question was posted here "Show the rows containing same values rate as column in html table": http://forums.asp.net/t/1239535.aspx

I came up a solution to use multiple CTEs, String concatenation, and PIVOT to get the result from SQL Server 2005. Just an interesting way to do it in database. There may be better way to do it from from end but that is not the point for this post.

CREATE

TABLE [dbo].[groupDataset$](

[City] [nvarchar]

(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Kg] [int]

NULL,

[Rate_rs] [decimal]

(8, 2) NULL

)

–Go

INSERT [dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Rajkot’, 1, CAST(25.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Rajkot’, 2, CAST(30.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Rajkot’, 3, CAST(42.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Ahemedabad’, 1, CAST(42.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Ahemedabad’, 2, CAST(55.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Ahemedabad’, 3, CAST(67.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Ahemedabad’, 4, CAST(80.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Goa’, 1, CAST(90.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Goa’, 2, CAST(105.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Goa’, 3, CAST(120.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gondal’, 1, CAST(25.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gondal’, 2, CAST(30.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gondal’, 3, CAST(42.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Morbi’, 1, CAST(25.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Morbi’, 2, CAST(30.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Morbi’, 3, CAST(42.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gandhinagar’, 1, CAST(42.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gandhinagar’, 2, CAST(55.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gandhinagar’, 3, CAST(67.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gandhinagar’, 4, CAST(80.00 AS Decimal(8, 2)))

Solution:

 

;WITH mycte

AS

(SELECT City, Kg, Rate_rs,

DENSE_RANK

() OVER (ORDER BY kg, Rate_rs) AS denseRankNum

FROM

groupDataset$

)

,

mycte2

AS

(

SELECT DISTINCT

Cities

= REPLACE(

(

SELECT

City

AS [data()] FROM

mycte t

WHERE

t

.denseRankNum = c.denseRankNum

ORDER BY

denseRankNum

, City

FOR XML PATH ()), ‘ ‘, ‘,’) , Kg, Rate_rs

FROM

mycte c

)

–SELECT * FROM mycte2 ORDER BY Kg, Rate_rs, cities

SELECT Cities, ISNULL(CAST([1] as varchar),‘-‘) as [1kg],

ISNULL

(CAST([2] as varchar),‘-‘) as [2kg],

ISNULL

(CAST([3] as varchar),‘-‘) as [3kg],

ISNULL(CAST([4] as varchar),‘-‘) as [4kg]

/*, ISNULL(CAST([5] as varchar),’-‘) as [5kg]

, ISNULL(CAST([6] as varchar),’-‘) as [6kg]

*/

— you can add more manually

FROM

(SELECT cities, Kg, Rate_rs FROM mycte2) AS t

PIVOT

(MAX(Rate_rs) FOR Kg IN ([1],[2],[3],[4]/*,[5],[6] */)) as pvt

ORDER

BY [1kg]

 
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s