如何格式化SQL中的字段单元格？ [英] How to format field cells in SQL?

问题描述

大家好。



我有一个SQL问题有点棘手。



我有两个表格，其中包含由行表格中的FK链接的标题和行。例如：

Hello every one.

I have an SQL question a little bit tricky.

I have two tables with headers and lines linked by a FK in the line table. For example:

Header
-------
PK_header
First_name
Last_name
...

和

and

Lines
-------
PK_lines
FK_header
Item_number
Quantity
Price

我希望这样格式化

I wish to format like this

PK_header First_name Last_name Item_number Quantity Price
1         fn1        ln1       123         1        12.99
                               345         2        4.99
                               789         1        5.49
2         fn2        ln2       159         1        2.99
                               753         2        1.49
3         fn3        ln3       675         1        0.99
...

我遇到的问题不是检索信息，而是按照上面的方式编制信息。



那怎么写这个呢在SQL for SQL Server中？



我希望你能看到我想要的东西。在此先感谢您的帮助。



你的，



弗雷德。



如上所述：

以下是检索我所做信息的查询：

The problem I have is not retrieving the information but formating them the way you can see above.

So how can write this in SQL for SQL Server?

I hope you see what I wish for. Thanks in advance for your help.

Yours,

Fred.

As asked:
Here is the query to retrieve informations I made:

select
header.PK, header.First_name, header.Last_name, ...
lines.Item_number, lines.Quantity, lines.price
from header
inner join lines
on header.PK = lines.PK_header

此查询显示每行的每个标题。我希望仅在PK更改时显示标题数据。



它适用于SQL Server 2000，它必须在存储过程中。

This query displays each header for each lines. I wish to display the header data only when the PK changes.

It's for SQL Server 2000 and it has to be in a stored procedure.

推荐答案

SQL只为行和列提供数据，如果您打算合并单元格，这取决于您的UI组件/层，这是另一回事。

SQL only gives you data in rows and columns, if you intend to "merge cells" that is up to your UI component/layer to do which is a different matter.

我通过使用这样的子查询解决了它：

I have solved it by using a sub query like this:

select 
  case when (l.PK_lines = d.minL) then convert(varchar(50), e.PK_header) else '' end as PK_header,
l.Item_number,
...
from
(
select FK_header, min(PK_lines) minL
from Lines
group by FK_header
) as d
inner join Header h
on e.PK_header = d.FK_header
inner join Lines l
on l.FK_header = d.FK_header
order by d.minL

有点......

sort of...

您可以通过Group by和Roll Up类获得确切的格式。下面找到我的查询。我只是手动创建了这个查询。请在您的数据库中尝试这个并告诉我。

Hi,

You get the exact format by Group by and Roll Up classes. below find my query. i have simply created this query manually. please try this in your DB and let me know.

SELECT CASE WHEN (Grouping(PK_header)=1) THEN 'MainTotal' ELSE PK_header END AS PK_header,
CASE WHEN (Grouping(First_name)=1) THEN 'SubTotal1' ELSE First_name END AS First_name,
CASE WHEN (Grouping(Last_name)=1) THEN 'SubTotal2' ELSE Last_name END AS Last_name,
CASE WHEN (Grouping(Item_number)=1) THEN 'SubTotal3' ELSE Item_number END AS Item_number,
CASE WHEN (Grouping(Quantity)=1) THEN 'SubTotal4' ELSE Quantity END AS Quantity,
Sum (case When Price>=0 then CASE_REF end ) as [Price] from Header,Lines
where  PK_header=Fk_header
 group by PK_header,First_name,Last_name,Item_number,Quantity WITH ROLLUP

如需更多帮助：



http://technet.microsoft.com/en-us/library/bb522495 （v = sql.105）.aspx [ ^ ]

这篇关于如何格式化SQL中的字段单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！