如何在tsql中将数据划分为多列

发布于 2024-09-07 23:46:46 字数 770 浏览 4 评论 0原文

我的数据库中有大约 1000 行，我想将它们按列组进行划分。我的意思是，在以下格式中，

SlNo.    Name    Price      SlNo.    Name    Price      SlNo.    Name    Price

我如何编写查询以显示上述格式的数据，如 rdlc 报告与 2008 年相比，我无法以这种格式显示数据。任何帮助将不胜感激...

销售表结构

CREATE TABLE [dbo].[Sales](
[SalesId] [int] IDENTITY(1,1) NOT NULL,
[MemoNo] [int] NULL,
[CustomerID] [int] NULL,
[SalesmanID] [int] NULL,
[DisRate] [int] NULL,
[CoolingCh] [int] NULL,
[GrandTotal] [int] NULL,
[SubTotal] [int] NULL,
[BillDate] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddedOn] [datetime] NULL,
 CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED 
(
[SalesId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

谢谢.........

原文

i have around 1000 of rows in database which i want to divide in column group wise. i mean in following format

SlNo.    Name    Price      SlNo.    Name    Price      SlNo.    Name    Price

how i can write query to show data in above format as in rdlc report vs 2008 i am unable to show data in this format. Any help would be appreciated...

Sales Table Structure

CREATE TABLE [dbo].[Sales](
[SalesId] [int] IDENTITY(1,1) NOT NULL,
[MemoNo] [int] NULL,
[CustomerID] [int] NULL,
[SalesmanID] [int] NULL,
[DisRate] [int] NULL,
[CoolingCh] [int] NULL,
[GrandTotal] [int] NULL,
[SubTotal] [int] NULL,
[BillDate] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddedOn] [datetime] NULL,
 CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED 
(
[SalesId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

thanks.........

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

青瓷清茶倾城歌 2024-09-14 23:46:46

这应该很简单，

SELECT 
    SalesId, [Name], GrandTotal as 'Price'
FROM 
    dbo.Sales 
WHERE
    (some condition)

可以检索您需要的所有数据，然后在 SQL Server 报表设计器中使您的报表显示重复 3 次的三列...

使用 SQL Server 和 T-SQL 检索数据< /strong>，并使用 SQL Server Reporting Services格式化输出以满足您的需要。

This should be a simple

SELECT 
    SalesId, [Name], GrandTotal as 'Price'
FROM 
    dbo.Sales 
WHERE
    (some condition)

to retrieve all the data you need, and then in the SQL Server Report Designer make your report display the three columns repeated three times across...

Use SQL Server and T-SQL to retrieve the data, and use SQL Server Reporting Services to format the output to your needs.

回复收藏 0 原文

述情 2024-09-14 23:46:46

我认为可以按照...

SELECT
    SELECT *
        FROM (SELECT SalesId, Name, Price
                  FROM Sales S2
                  WHERE S2.SalesId = S1.SalesId    ) as Tab1,
    SELECT *
        FROM (SELECT SalesId, Name, Price
                  FROM Sales S2
                  WHERE S2.SalesId = S1.SalesId + 1) as Tab2,
    SELECT *
        FROM (SELECT SalesId, Name, Price
                  FROM Sales S2
                  WHERE S2.SalesId = S1.SalesId + 2) as Tab3
    FROM Sales
    WHERE ...every third row...;

我无法填写完整的详细信息，因为我不是 TSQL 专家，而且该表不包含您可能必须填写的 SlNo、名称或价格从某处得到。这是否是罗纳德·维尔登伯格（Ronald Wildenberg）建议的编码的改进取决于品味问题。

I think something could be done along the lines of...

SELECT
    SELECT *
        FROM (SELECT SalesId, Name, Price
                  FROM Sales S2
                  WHERE S2.SalesId = S1.SalesId    ) as Tab1,
    SELECT *
        FROM (SELECT SalesId, Name, Price
                  FROM Sales S2
                  WHERE S2.SalesId = S1.SalesId + 1) as Tab2,
    SELECT *
        FROM (SELECT SalesId, Name, Price
                  FROM Sales S2
                  WHERE S2.SalesId = S1.SalesId + 2) as Tab3
    FROM Sales
    WHERE ...every third row...;

I can't fill in the full details because I'm no expert on TSQL, and also the table doesn't contain a SlNo, Name or Price which you presumably have to get from somewhere. Whether that's an improvement on going to code as Ronald Wildenberg suggests is a matter of taste.

回复收藏 0 原文

~没有更多了~