从SQL数据库中获取数据
问题描述：
大家好
下面我给出了表格
Hi All
Below I given the Table
Rack cell qty
A1 X 20
A1 Y 30
A1 Z 40
B1 X 70
B1 Y 60
C1 Z 34
C1 z 22
select A.Rack,
(select sum(B.Qty) from TestForAccess B where B.Rack =A.Rack and B.Cell='x') as'X',
(select sum(B.Qty) from TestForAccess B where B.Rack =A.Rack and B.Cell='y') as'Y',
(select sum(B.Qty) from TestForAccess B where B.Rack =A.Rack and B.Cell='z') as'Z',
Count(A.Rack)
from TestForAccess A
group by A.Rack
执行查询后，我是如下表
after executing the query I am the table like below
Rack X Y Z
A1 20 30 40
B1 70 60 Null
C1 Null Null 56
现在，如果我添加另一个机架，如
Now if I add another rack like
rack cell qty
A2 y 10
它工作正常，预期输出
it is working fine it expected output
Rack X Y Z
A1 20 30 40
A2 null 10 null >new line added
B1 70 60 Null
C1 Null Null 56
但是如果添加另一个像
的单元格
But if add another cell like
rack cell qty
A1 m 20
问题-
这里的单元格m是新单元格，它不像
那样提供输出
PROBLEM-
here cell m is new cell which is not give output like
Rack X Y Z m > this column should add which is Not giving
A1 20 30 40 20
A2 null 10 null null
B1 70 60 Null null
C1 Null Null 56 null
有什么想法请帮忙
谢谢
Any idea please help
Thanks
答
请尝试以下方法.
Hi,
Try the below approach.
CREATE TABLE #Temp
(
Rack VARCHAR(50),
cell VarchAR(50),
qty INT
)
INSERT INTO #Temp
SELECT ''A1'', ''X'', 20 UNION
SELECT ''A1'', ''Y'', 30 UNION
SELECT ''A1'', ''Z'', 40 UNION
SELECT ''B1'', ''X'', 70 UNION
SELECT ''B1'', ''Y'', 60 UNION
SELECT ''C1'', ''Z'', 34 UNION
SELECT ''C1'', ''z'', 22
UNION
SELECT ''A2'', ''y'', 10
UNION
SELECT ''A1'', ''m'', 20
--SELECT * FROM #Temp
/*Take the distinct cell values from #temp table and insert it into #temp2 table*/
SELECT DISTINCT IDENTITY(INT,1,1) AS ID , Cell INTO #Temp2 FROM #Temp
--SELECT * FROM #Temp2
DECLARE @Count INT
DECLARE @TotalRows INT
DECLARE @ColName VARCHAR(MAX)
DECLARE @SQL VARCHAR(MAX)
/*Table to show final result*/
CREATE TABLE #Temp3
(
Rack VARCHAR(50)
)
/*Insert all the Rack values from #temp table to the final table*/
INSERT INTO #Temp3
SELECT DISTINCT Rack FROM #Temp
SELECT @Count = 1, @TotalRows = COUNT(*) FROM #Temp2
/*Loop through the rows of #temp2 table and add the cell values of table #temp2 as new column to table #temp3*/
WHILE @Count <= @TotalRows
BEGIN
SELECT @ColName = Cell FROM #Temp2 WHERE ID = @Count
SET @SQL = ''ALTER TABLE #Temp3 ADD '' + @ColName + '' INT''
PRINT @SQL
EXECUTE (@SQL)
/*Once a new column is added set the values of the new column for the corresponding rack */
SET @SQL = ''UPDATE T3 SET ''+ @ColName + '' = Val FROM #Temp T1''
SET @SQL = @SQL + '' inner join (select Rack, SUM(qty) AS Val FROM #Temp WHERE cell = ''''''+ @ColName +'''''' GROUP BY Rack) T ON T1.Rack = T.Rack''
SET @SQL = @SQL + '' INNER JOIN #Temp3 T3 ON T1.Rack = T3.Rack''
PRINT @Sql
Execute(@sql)
SET @Count = @Count + 1
END
SELECT * FROM #Temp3
DROP TABLE #TEMp
DROP TABLE #TEMp2
DROP TABLE #temp3
以下查询更短，更易于维护:
Following query is shorter and more easily maintainable :
select *
from (select rack,cell,qty from TestForAccess) as D
pivot(sum(qty) for cell in ([X],[Y],[Z],[M])) as P;
您仍然必须预先了解单元格，我正在寻找一种无需使用存储过程的解决方案.
如果您想要灵活性，这就是做到这一点的方法:
you still have to know up front the cells though, I am looking for a solution for this without resorting to a stored procedure.
if you want flexibility this is the way to do it :
DECLARE @query NVARCHAR(4000)
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ',['+ cell + ']','['+ cell + ']') from (select distinct cell from TestForAccess) as T
print @cols
SET @query = 'select rack,'+@cols+' from (select rack,cell,qty from TestForAccess) as D pivot(sum(qty) for cell in ('+@cols+')) as P;'
print @query
execute(@query)
好的如何在不进行数据透视的情况下执行此操作，以使其在sql 2000中起作用
ok how to do this without pivot so that it works in sql 2000
select rack,
SUM(case when cell='X' then qty end) as x,
SUM(case when cell='Y' then qty end) as y,
SUM(case when cell='Z' then qty end) as z,
SUM(case when cell='M' then qty end) as m
from TestForAccess
group by rack