How to create a pivot query in sql server without aggregate function

Question

5.00/5 (2 votes)

See more:

SQL-Server-2008R2

I am using MS SQL SERVER 2008 and I have following data:

TRXID	ITEM_HEAD_ID	ITEM_HEAD_AMT
6	1	100.00
6	2	100.00
6	3	300.00
7	1	100.00
7	2	100.00
7	3	300.00

I want to make them to be this:

TRXID	1	2	3	Total
6	100	100	300	500
7	100	100	300	500

I've tried to query use pivot query but the value have to use aggregate function and the result is not appropriate. what should I do?

Posted 18-Apr-14 22:37pm

vishal_h

Add a Solution

Comments

Andrius Leonavicius 19-Apr-14 8:39am

Hi,

The column "ITEM_HEAD_ID" values are 1, 2 and 3. And you want to transform rows into columns. Is it static or dynamic transformation?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andrius Leonavicius · Answer 1 · 2014-04-19T11:33:00

You can't remove the aggregate function, but you can construct your query according to your needs. You can do this at least in two ways:

1. Static PIVOT query.
For example, if the column "ITEM_HEAD_ID" values can only be like 1, 2 and 3, then this is what you need. According to your given data, you can use the following query:

SQL

SELECT *, (ISNULL([1], 0 ) + ISNULL([2], 0) + ISNULL([3], 0)) AS [Total]
FROM   (SELECT [TRXID],
               [ITEM_HEAD_ID],
               [ITEM_HEAD_AMT]
        FROM   [Table]) AS t
       PIVOT (MAX([ITEM_HEAD_AMT])
             FOR [ITEM_HEAD_ID] IN ([1], [2], [3])) AS p;

Note: [Table] is the name of your table.
The result:

XML

TRXID	1	2	3	Total
6	100.00	100.00	300.00	500.00
7	100.00	100.00	300.00	500.00

2. Dynamic PIVOT query.
If the column "ITEM_HEAD_ID" values are not static, then this is what you need. Let's say that you have the following data:

XML

TRXID	ITEM_HEAD_ID	ITEM_HEAD_AMT
6	1		100.00
6	2		100.00
6	3		300.00
7	1		100.00
7	2		100.00
7	3		300.00
7	4		1.00

Example of dynamic query:

SQL

DECLARE @ColumnsTable TABLE ([ColumnName] VARCHAR(50));

INSERT INTO @ColumnsTable ([ColumnName])
SELECT DISTINCT '[' + CONVERT(VARCHAR(48), [ITEM_HEAD_ID]) + ']'
FROM   [Table];

DECLARE @PivotColumns VARCHAR(MAX), @TotalColumn VARCHAR(MAX), @SQL VARCHAR(MAX);

SET @PivotColumns = (SELECT STUFF((SELECT DISTINCT ', ' + CONVERT(VARCHAR(50), [ColumnName])
                                   FROM   @ColumnsTable
                                   FOR XML PATH('')), 1, 2, ''));
SET @TotalColumn = (SELECT STUFF((SELECT DISTINCT ' + ISNULL(' + CONVERT(VARCHAR(50), [ColumnName]) + ', 0)'
                                  FROM   @ColumnsTable
                                  FOR XML PATH('')), 1, 3, ''));
SET @SQL = 'SELECT *, (' + @TotalColumn + ') AS [Total]
FROM   (SELECT [TRXID],
               [ITEM_HEAD_ID],
               [ITEM_HEAD_AMT]
        FROM   [Table]) AS t
       PIVOT (MAX([ITEM_HEAD_AMT])
             FOR [ITEM_HEAD_ID] IN (' + @PivotColumns + ')) AS p;';

EXEC(@SQL);

The result:

XML

TRXID	1	2	3	4	Total
6	100.00	100.00	300.00	NULL	500.00
7	100.00	100.00	300.00	1.00	501.00

Please let me know how it works for you.