以列方式显示行

问题描述：

亲爱的朋友，

我想像5列视图一样显示5行.

EG:
1. Mob1
2. Mob2
3. Mob3

它应显示为Mob1 Mob2 Mob3

我必须从SQl开始.

问候，
AP

Dear Friends,

I want to display the 5 row like 5 columns view.

EG:
1. Mob1
2. Mob2
3. Mob3

Its should displayed as Mob1 Mob2 Mob3

I have to it from SQl.

Regards,
AP

答

PIVOT 关系运算符在这里 ^ ]可以用于此目的，如下所示:

The PIVOT relational operator explained here http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspx[^] can be used for this purpose as shown below:

IF OBJECT_ID('Data','U') is NOT NULL
    DROP TABLE Data

CREATE TABLE [dbo].[Data](
    [SlNum] [int] NULL,
    [Name] [nvarchar](50) NULL,
) ON [PRIMARY]
GO

INSERT INTO Data(SlNum, Name)
VALUES(1, 'Mob1')
INSERT INTO Data(SlNum, Name)
VALUES(2, 'Mob2')
INSERT INTO Data(SlNum, Name)
VALUES(3, 'Mob3')
INSERT INTO Data(SlNum, Name)
VALUES(4, 'Mob4')
INSERT INTO Data(SlNum, Name)
VALUES(5, 'Mob5')

SELECT Mob1, Mob2, Mob3, Mob4, Mob5
FROM
    (SELECT SlNum, Name FROM Data) AS SourceTable
PIVOT
    (MIN(SlNum) FOR Name IN (Mob1, Mob2, Mob3, Mob4, Mob5)) AS PIVOTTABLE

--Output
--Mob1 Mob2 Mob3 Mob4 Mob5
-- 1	2    3    4    5

这也适用于动态数据..

This works fine for dynamic data too..

Select my_Row1=max(isnull(case when rn=1 then mob_no end,'')),
my_Row2=max(isnull(case when rn=2 then mob_no end,'')),
my_Row3=max(isnull(case when rn=3 then mob_no end,'')),
my_Row4=max(isnull(case when rn=4 then mob_no end,'')),
my_Row5=max(isnull(case when rn=5 then mob_no end,'')) from (
Select top 5 RN=ROW_NUMBER () OVER (order by mob_no),mob_no from myDB..my_TABLE a with (nolock))j

问候，
AP

Regards,
AP

相关推荐