如何在SQL Server 2005/8上获得类表查询结果?

I have 3 tables:

我有3个表:

users (id, name)

currency (id, name)

accounts (id, user_id, currency_id, amount)

And I want to read the data from accounts and present it in table-like view:

我想从账户中读取数据，并以表格形式呈现出来:

owner  currency1 currency2 currency3
1      0         0         0
2     10        20        30
3      0         5        10

Where owner is ID of accounts.owner, currency1,2,3 - (SELECT id FROM currency WHERE name = '1',etc)

所有者是帐户的ID。所有者，currency1,2,3 -(从货币中选择id = '1'等)

I can get such result only for one specific ID:

我只能得到一个特定ID的结果:

SELECT
  SELECT amount FROM accounts WHERE currency = (SELECT id FROM currency WHERE name = 'currency1') AND owner = @user) AS [currency1],
  SELECT amount FROM accounts WHERE currency = (SELECT id FROM currency WHERE name = 'currency2') AND owner = @user) AS [currency2],
  SELECT amount FROM accounts WHERE currency = (SELECT id FROM currency WHERE name = 'currency2') AND owner = @user) AS [currency2]

Is it possible to get the same result for every object in users table? Without using Reporing Service, etc.

是否可能为users表中的每个对象获得相同的结果?不使用报告服务等。

2 个解决方案

#1

Use a pivot table and dynamic SQL to retrieve the columns

使用pivot表和动态SQL检索列

    DECLARE @columns VARCHAR(2000)
    SELECT @columns = STUFF(( SELECT DISTINCT TOP 100 PERCENT
    '],[' + c.name
    FROM currency AS c
    ORDER BY '],[' + c.name
    FOR XML PATH('')
    ), 1, 2, '') + ']'

    DECLARE @query NVARCHAR(4000)
    SET @query = N'SELECT UserName, ' + @columns +
    'FROM 
    (SELECT u.Name AS UserName, c.name AS CurrencyName, a.Amount
    FROM Accounts AS a WITH(NOLOCK)
    JOIN Users u WITH(NOLOCK) ON a.user_id = u.user_id
    JOIN Currency c WITH(NOLOCK) ON a.currency_id = c.currency_id
    ) p
    PIVOT
    (
    SUM (p.Amount)
    FOR p.CurrencyName IN
    ( '+ @columns +')
    ) AS pvt
    ORDER BY UserName'

EXECUTE(@query)

This was tested in SQL Server 2005

这是在SQL Server 2005中测试的。

#2

Sounds like you want a Pivot table. It will be difficult to do if you have a varying number of rows in currency, but could still be done by using dynamiclly written sql.

听起来你想要一个数据透视表。如果您有不同数量的货币行，这将很难实现，但是仍然可以使用动态编写的sql来实现。

Here's a resource from MSDN that explains how to use the pivot table: http://msdn.microsoft.com/en-us/library/ms177410.aspx

下面是来自MSDN的一个资源，它解释了如何使用pivot表:http://msdn.microsoft.com/en-us/library/ms177410.aspx

SELECT u.name, [1] AS Currency1, [2] AS Currency2, [3] AS Currency3
FROM 
(SELECT u.Name AS UserName, c.Currency_ID, a.Amount
FROM Accounts AS a WITH(NOLOCK)
JOIN Users u WITH(NOLOCK) ON a.user_id = u.user_id
) p
PIVOT
(
SUM (p.Amount)
FOR p.Currency_id IN
( [1], [2], [3] )
) AS pvt
ORDER BY pvt.UserName

#1