Sql，如何通过将行转换为列来使用表进行查看 [英] Sql, how to make view using a table by transform rows into columns

问题描述

你好



我需要你的帮助。

我想在SQL中查看视图，首先如果我有这个表：



ID Po

1 A

2 A

3 A

4 B

5 A

6 C

7 D

8 E



如何使用select to制作视图，视图会给我这样的表格：



ID Po A Po B Po C Po D Po E

1 A B C D E

2 A - - - -

3 A - - - -

4 A - - - -



这里ID会自动递增。



我希望它很清楚。我很抱歉，但我尝试了很多来解决这个问题，我真的需要它，所以请各位帮助我。



非常感谢所有的东西



我尝试过的事情：



我找不到好办法和我所有的尝试离我正在看的东西很远

Hello

please I need your help.
I am trying to make a view in SQL, first if I have this table:

ID Po
1 A
2 A
3 A
4 B
5 A
6 C
7 D
8 E

How can I make a view using select to and the view will give me the table like this:

ID Po A Po B Po C Po D Po E
1 A B C D E
2 A - - - -
3 A - - - -
4 A - - - -

and here the ID will be auto increment.

I hope it's clear. I am sorry but I tried a lot to solve this and I really need it so please guys help me.

Thank a lot for every things

What I have tried:

I didn't find the good way and all my tried were very far from what I am looking

推荐答案

由于最终表格中的数据不是很清楚如何从你的源数据到你的预期数据表示你原来的内容。



但是，正如@ 0x01AA所述，你需要在某个时候使用PIVOT。 CodeProject上有一个简短的提示，它提供了一个很好的简单示例：在SQL查询中使用Pivot的简单方法 [ ^ ]



以下是您可能想要对源数据执行的操作的示例：

It's not at all clear how to get from your source data to your expected data as the data in the final table does not represent what was in your original.

However, as @0x01AA stated, you need to use a PIVOT at some point. There is a short tip here on CodeProject that gives a nice simple example:Simple Way To Use Pivot In SQL Query[^]

Here is an example of what you might want to do with your source data:

create table #demo (ID integer identity(1,1), Po varchar(10))
insert into #demo (Po) values
('A'), ('A'), ('A'), ('B'), ('A'), ('C'), ('D'), ('E')

SELECT *
FROM (
    SELECT ID, Po
        FROM #demo
) as s
PIVOT
(
    COUNT(ID)
    FOR [Po] IN (A, B, C, D, E)
)AS pvt

给出结果

A	B	C	D	E
4	1	1	1	1

如果你想做其他的事情你可能想把它放到公用表表达式（CTE）中，请参阅 SQL Wizardry第三部分 - 公用表表达式（CTE） [ ^ ]



你还提到了结果中的自动增量ID。这不太可能是你表中的ID ...在结果中得到类似结果的好方法是使用 ROW_NUMBER [ ^ ]功能。以下是使用与上述相同数据的简单示例：

If you want to do other stuff you might want to put this into a Common Table Expression (CTE) see SQL Wizardry Part Three - Common Table Expressions (CTEs)[^]

You also mentioned an auto-increment ID in your results. It is unlikely that this is going to be the ID from your table... a good way of getting a similar result in results is to use the ROW_NUMBER[^] function. Here is a trivial example using the same data as above:

SELECT ID, Po, ROW_NUMBER() OVER (ORDER BY ID DESC)
FROM #demo
ORDER BY ID

如果我离开这个刚刚回复的基础，我会尝试进一步帮助

If I'm way off base with this just reply and I'll try to help further

这篇关于Sql，如何通过将行转换为列来使用表进行查看的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！