November 27, 2016 at 9:59 pm
Have you done any SQL code/set of SQL for below sample data and results?
Sample Data
Site_IDName Address CityPostalType
12345John123 Main St MarkhamL6E2H5HIP
12345john123 Main StMarkhamL6E2H5CIP
12345john123 Main StMarkhamL6E2H5PDP
Final Results
Site_idName Address CityPostal Plan1 Plan2 Plan3
12345John123 Main St MarkhamL6E2H5HIP CIP PDP
Any Help
Thanks,
Ranjith
November 27, 2016 at 10:50 pm
You want to look into the PIVOT relational operator
November 28, 2016 at 1:19 am
Two examples, one using Pivot and the other using CrossTab
😎
Pivot
USE TEEST;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(Site_ID,Name,Address,City,Postal,Type) AS
( SELECT X.Site_ID,X.Name,X.Address,X.City,Postal,X.Type
FROM (VALUES
(12345,'John','123 Main St','Markham','L6E2H5','HIP')
,(12345,'john','123 Main St','Markham','L6E2H5','CIP')
,(12345,'john','123 Main St','Markham','L6E2H5','PDP')
) X(Site_ID,Name,Address,City,Postal,Type)
)
SELECT
Site_ID
,Name
,Address
,City
,Postal
,[1] AS [Plan1]
,[2] AS [Plan2]
,[3] AS [Plan3]
FROM
(
SELECT
SD.Site_ID
,SD.Name
,SD.Address
,SD.City
,SD.Postal
,SD.Type
,ROW_NUMBER() OVER
(
PARTITION BY SD.Site_ID
ORDER BY SD.Type
) AS SRID
FROM SAMPLE_DATA SD
) AS STB
PIVOT
(
MAX(Type)
FOR SRID IN ([1],[2],[3])
) AS XP;
CrossTab
USE TEEST;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(Site_ID,Name,Address,City,Postal,Type) AS
( SELECT X.Site_ID,X.Name,X.Address,X.City,Postal,X.Type
FROM (VALUES
(12345,'John','123 Main St','Markham','L6E2H5','HIP')
,(12345,'john','123 Main St','Markham','L6E2H5','CIP')
,(12345,'john','123 Main St','Markham','L6E2H5','PDP')
) X(Site_ID,Name,Address,City,Postal,Type)
)
,BASE_DATA AS
(
SELECT
SD.Site_ID
,SD.Name
,SD.Address
,SD.City
,SD.Postal
,SD.Type
,ROW_NUMBER() OVER
(
PARTITION BY SD.Site_ID
ORDER BY SD.Type
) AS SRID
FROM SAMPLE_DATA SD
)
SELECT
BD.Site_ID
,BD.Name
,BD.Address
,BD.City
,BD.Postal
,MAX(CASE WHEN BD.SRID = 1 THEN BD.Type END) AS Plan1
,MAX(CASE WHEN BD.SRID = 2 THEN BD.Type END) AS Plan2
,MAX(CASE WHEN BD.SRID = 3 THEN BD.Type END) AS Plan3
FROM BASE_DATA BD
GROUP BY BD.Site_ID
,BD.Name
,BD.Address
,BD.City
,BD.Postal;
Output (identical)
Site_ID Name Address City Postal Plan1 Plan2 Plan3
----------- ---- ----------- ------- ------ ----- ----- -----
12345 john 123 Main St Markham L6E2H5 CIP HIP PDP
November 28, 2016 at 6:21 am
Thanks You.
What about if I have Price1, Price2 and price3? Should use the cross tab query breakdown the price.
November 28, 2016 at 6:55 am
ranjith.sivanathan (11/28/2016)
Thanks You.What about if I have Price1, Price2 and price3? Should use the cross tab query breakdown the price.
Use the CROSS TAB query Eirikur posted and add the extra columns. It's that simple. If you don't understand how it works (and you should... you are the one that'll need to support it), please see the following articles on this subject.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply