Wie erhält man ein tabellenähnliches Abfrageergebnis auf SQL Server 2005/8?

Question

Ich habe 3 Tabellen:

users (id, name)

currency (id, name)

accounts (id, user_id, currency_id, amount)

Und ich möchte die Daten lesen von accounts und in einer tabellenähnlichen Ansicht darstellen:

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

Wo owner es ID de accounts.owner , currency1,2,3 - (SELECT id FROM currency WHERE name = '1',etc)

Ich kann dieses Ergebnis nur für eine bestimmte ID erhalten:

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]

Ist es möglich, das gleiche Ergebnis für jedes Objekt in users Tisch? Ohne Verwendung des Reporters usw.

Answer 1

Das klingt, als ob Sie eine Pivot-Tabelle benötigen. Das wird schwierig sein, wenn Sie eine unterschiedliche Anzahl von Zeilen in der Währung haben, aber es könnte immer noch mit dynamisch geschriebenem Sql gemacht werden.

Hier ist eine Ressource von MSDN, die erklärt, wie man die Pivot-Tabelle verwendet: 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

Answer 2

Verwenden Sie eine Pivot-Tabelle und dynamisches SQL, um die Spalten abzurufen

    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)

Dies wurde in SQL Server 2005 getestet.