In SQL Server 2005, I have a table say tbl_Info. There I have one column say Info.
在SQL Server 2005中,我有一个表说tbl_Info。我有一栏说信息。
The values are as under
值如下
Name1 Age1 Sex1 Name2 Age2 Sex2 ..... ..... .....
And the row values goes on like this.
行值继续这样。
My question is how to make row col transposition.
我的问题是如何进行行col转换。
The desired output will be
期望的输出将是
Name Age Sex Name1 Age1 Sex1 Name2 Age2 Sex2 ..... .... ........ ..... .... ........
You can use a table variable or temp table.
您可以使用表变量或临时表。
And please don't give the solution by using assemblies. Because I already did that by using assemblies but I want a solution which I can even use in SQL Server 2000.
请不要使用程序集给出解决方案。因为我已经通过使用程序集实现了这一点,但我想要一个我甚至可以在SQL Server 2000中使用的解决方案。
2 个解决方案
#1
The general idea is that you need another column to guarantee order -- that's typically going to be a primary key of some integer type, but to keep things simple say we just use:
一般的想法是你需要另一个列来保证顺序 - 这通常是一些整数类型的主键,但为了简单起见,我们只需使用:
CREATE TABLE foo (ord INT, col VARCHAR)
with ord just inserted as 0, 1, 2, etc.
ord刚刚插入为0,1,2等。
Now the SELECT you want is:
现在你想要的SELECT是:
SELECT a.col AS Name, b.col AS Age, c.col AS Sex
FROM foo AS a
JOIN foo AS b ON(b.ord=1+a.ord)
JOIN foo AS c ON(c.ord=2+a.ord)
WHERE a.ord%3=0
which will work on just about _any_thing that dares call itself "SQL";-).
这将只适用于敢于称自己为“SQL”的_any_thing ;-)。
For example, when the data are:
例如,当数据是:
ord col
0 John
1 23
2 M
3 Mary
4 21
5 F
6 Karl
7 25
8 M
the above SELECT's result is:
以上SELECT的结果是:
Name Age Sex
John 23 M
Mary 21 F
Karl 25 M
#2
You want to pivot the table. Normally you want at least two columns, though (one for a key and one for a value) and you also generally need to know in advance what columns you are expecting in the results.
您想要转动表格。通常,您希望至少有两列(一个用于键,一个用于值),并且您通常还需要事先知道结果中预期的列。
#1
The general idea is that you need another column to guarantee order -- that's typically going to be a primary key of some integer type, but to keep things simple say we just use:
一般的想法是你需要另一个列来保证顺序 - 这通常是一些整数类型的主键,但为了简单起见,我们只需使用:
CREATE TABLE foo (ord INT, col VARCHAR)
with ord just inserted as 0, 1, 2, etc.
ord刚刚插入为0,1,2等。
Now the SELECT you want is:
现在你想要的SELECT是:
SELECT a.col AS Name, b.col AS Age, c.col AS Sex
FROM foo AS a
JOIN foo AS b ON(b.ord=1+a.ord)
JOIN foo AS c ON(c.ord=2+a.ord)
WHERE a.ord%3=0
which will work on just about _any_thing that dares call itself "SQL";-).
这将只适用于敢于称自己为“SQL”的_any_thing ;-)。
For example, when the data are:
例如,当数据是:
ord col
0 John
1 23
2 M
3 Mary
4 21
5 F
6 Karl
7 25
8 M
the above SELECT's result is:
以上SELECT的结果是:
Name Age Sex
John 23 M
Mary 21 F
Karl 25 M
#2
You want to pivot the table. Normally you want at least two columns, though (one for a key and one for a value) and you also generally need to know in advance what columns you are expecting in the results.
您想要转动表格。通常,您希望至少有两列(一个用于键,一个用于值),并且您通常还需要事先知道结果中预期的列。