T-SQL(与天蓝色兼容)从一列获取值作为一行数据

本文介绍了T-SQL(与天蓝色兼容)从一列获取值作为一行数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我无法从表中查询查询.我有一个产品表和一个图像表

I am having trouble getting a query from a table. I have a Product Table and an Images table

[图像] 表架构[列名称:数据类型]

[Images] table schema [Column name:data type]

[ImagetId: int ] [ProductId: int ] [url: nvarchr(max)] [Size50: bit ] [Size100: bit ] [Size200: bit ] [Size400: bit ] [Size600: bit ] [Size800: bit ]

[ImagetId:int] [ProductId:int] [url:nvarchr(max)] [Size50:bit] [Size100:bit] [Size200:bit][Size400:bit] [Size600:bit] [Size800:bit]

[产品] 表架构

[ProductId: int ] [名称: nvarchar(50)]其他列...

[ProductId:int] [Name:nvarchar(50)] additional columns...

images表中每个产品都有6条记录，而Images表中的Bit/Boolean字段指定了网址指向的图像大小.

The images table has 6 records for each Product and the Bit/Boolean fields from the Images table specify what size of image the url points to.

我想获取一行数据中每种产品的6种尺寸图片的每个URL:

I want to get each URL for the 6 sizes of images for each product in a row of data:

所需查询架构

         [ProductId]  [url50]      [url100]   [url200]   [url400]   [url600]   [url800]
 --------------------------------------------------------------------------------------
 row 1  |     1      | http:...  | http:    | http:    | http:    | http:    | http:
 --------------------------------------------------------------------------------------
 row 2  |     2      | http:...  | http:    | http:    | http:    | http:    | http:
 --------------------------------------------------------------------------------------
 row 3  |     3      | http:...  | http:    | http:    | http:    | http:    | http:
 --------------------------------------------------------------------------------------
 row 4  |     4      | http:...  | http:    | http:    | http:    | http:    | http:

我尝试过的东西

我厌倦了在图像表上为每个图像大小使用内部联接，如下所示:

What I've Tried

I tired to use inner joins on the image table for each image size like the following:

SELECT  KrisisStore_Images.ImageId
        , KrisisStore_Images.PortalId
        , KrisisStore_Images.ProductId
        , KrisisStore_Images.Name
        , KrisisStore_Images.Description
        , KrisisStore_Images.PrimaryImage
        , Thumb50.Url AS UrlThumb50
        , Thumb100.Url AS UrlThumb100
FROM    KrisisStore_Images 
        INNER JOIN (SELECT ImageId, Url FROM KrisisStore_Images AS KrisisStore_Images_1 WHERE (Thumb50 = 1)
                    ) AS Thumb50 ON KrisisStore_Images.ImageId = Thumb50.ImageId 
        INNER JOIN (SELECT ImageId, Url FROM KrisisStore_Images AS KrisisStore_Images_2 WHERE (Thumb100 = 1)
                    ) AS Thumb100 ON KrisisStore_Images.ImageId = Thumb100.ImageId

但这不会产生结果

如果将联接类型更改为LEFT OUTER JOIN，则将获得每个图像尺寸的记录行，而不是像我想要的每个产品.

If I change the join type to LEFT OUTER JOIN then I get a record row for each image size, not each product Like I want.

我也尝试过使用Pivot(我从未使用过)，但是我无法根据位数据类型弄清楚我们该怎么做，而且我不需要任何聚合函数.

I also tried using Pivot (which I have never used) but I could not figure our how to do it based on the bit data type and I don't need any aggregate functions.

有人可以帮我获取6个图片尺寸网址中的每个网址，并将其作为一行显示在每个产品ID的一行中.另外，我需要它与Sql Azure兼容.

Can someone help me get each of the 6 image size URLs as a column in a single row for each product ID. Also, I need it to be Sql Azure compatible.

提前谢谢.

bit