Fetch data in a single Row

I have two tables PackageDetail and PackageDuration PackageDuration have the PackageID as Foreign Key i.e. can have multiple records with respect to PackageID

The Schema of the PackageDetail is:

PackageID  INT PK
PackageName Nvarchar(50)

Schema of the PackageDuration Table is:

DurationID INT PK
Price   Money
Duration Nvarchar(50)
PackageID INT FPK

PackageDetail tables have follwoing records:

PackageID        PackageName
  1              TestPackage
  2 开发者_开发知识库             MySecondPackage

PackageDuration table have following records:

DurationID      PackageID    Price        Duration
  1                 1        100          6
  2                 1        200          12
  3                 1        300          24
  4                 2        500          6

PackageDuration table can have max 3 records with one PackageID not more than this(if have ignore that) Now I want to select the Records as in following Way:

PackageId  PackageNAme     Price1 Price2 Price3 Duration1  Duration2    Duration3
1          TestPackage        100    200    300         6         12           24
2          MySecondPackage    500   null   null         6       null         null

Please suggest me how can I achive this.

Another approach:

WITH Durations AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY PackageId ORDER BY DurationId) Sequence
    FROM PackageDuration 
)

SELECT  A.PackageId, B.PackageName, 
        MIN(CASE WHEN Sequence = 1 THEN Price ELSE NULL END) Price1,
        MIN(CASE WHEN Sequence = 2 THEN Price ELSE NULL END) Price2,
        MIN(CASE WHEN Sequence = 3 THEN Price ELSE NULL END) Price3,
        MIN(CASE WHEN Sequence = 1 THEN Duration ELSE NULL END) Duration1,
        MIN(CASE WHEN Sequence = 2 THEN Duration ELSE NULL END) Duration2,
        MIN(CASE WHEN Sequence = 3 THEN Duration ELSE NULL END) Duration3
FROM Durations A
INNER JOIN PackageDetail B
ON A.PackageId = B.PackageId
GROUP BY A.PackageId, B.PackageName

This should work as long as the durations are unique for a package and they are either 6, 12, or 24.

SELECT 
    PackageDetail.PackageId, PackageDetail.PackageName, 
    D1.Price as Price1, D2.Price as Price2, D3.Price as Price3,
    D1.Duration as Duration1, D2.Duration as Duration2, D3.Duration as Duration3
FROM PackageDetail
LEFT OUTER JOIN PackageDuration D1 
    ON D1.PackageId = PackageDetail.PackageId AND D1.Duration = 6
LEFT OUTER JOIN PackageDuration D2 
    ON D2.PackageId = PackageDetail.PackageId AND D2.Duration = 12
LEFT OUTER JOIN PackageDuration D3 
    ON D3.PackageId = PackageDetail.PackageId AND D3.Duration = 24

;WITH pvt AS
(
   SELECT PackageID,
      Price1 = MAX(CASE WHEN Duration = 6 THEN Price END),
      Price2 = MAX(CASE WHEN Duration = 12 THEN Price END),
      Price3 = MAX(CASE WHEN Duration = 24 THEN Price END),
      Duration1 = MAX(CASE WHEN Duration = 6 THEN 6 END),
      Duration2 = MAX(CASE WHEN Duration = 12 THEN 12 END),
      Duration3 = MAX(CASE WHEN Duration = 24 THEN 24 END)
   FROM dbo.PackageDuration
   GROUP BY PackageID
)
SELECT
   pvt.PackageID,
   p.PackageName,
   pvt.Price1, pvt.Price2, pvt.Price3,
   pvt.Duration1, pvt.Duration2, pvt.Duration3
FROM
   dbo.PackageDetail AS p
INNER JOIN 
   pvt ON p.PackageID = pvt.PackageID
ORDER BY p.PackageID;

Maybe I'm missing something in the requirements, but it seems like Sql Server's PIVOT is what you're looking for.

There are quite a few questions here at SO about PIVOT... Here's a good clean example with references to other questions: How do i transform rows into columns in sql server 2005

The big benefit of a pivot table over the other answers here is that it will scale out with no modifications if you add records to your PackageDuration table in the future.