PIVOT Query for BIT Field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

PIVOT Query for BIT Field

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-02-27 : 22:47:59

Hi,
Need a query to build using Pivot for Bit field and have to find total for amount field.

TABLE:
[ID] [CUSTID] [BARANCHID] [ACTIVE] [AMT]
1 101 1 1 100
2 101 2 1 100
3 101 3 0 100
4 101 4 0 100
5 101 5 1 100
6 102 1 0 100
7 102 2 1 100
8 102 3 1 100
9 102 4 1 100
10 102 5 0 100

In the above table ACTIVE column is a BIT Field and the amount must be grouped for CUSTID column. For only Active ID's.
And the BRANCHID must be shown in columns and its values must be from the ACTIVE column.

EXPECTED RESULT:
[BRANCH/CUSTOMER] [1] [2] [3] [4] [5] [TOTALAMOUNT]
101 1 1 0 0 1 300
102 0 1 1 1 0 300

Please help me to get the solution. Its a sample table, actual table will holds 25000 rows and the result table will have min 50 rows, hence I prefer to do this with PIVOT Query.

Regards,
Kalaiselvan R
Love Yourself First....

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2012-02-27 : 23:10:25

hi

you can refer to this post.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=171729

Hope this helps

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-02-28 : 06:40:27

Sorry I cant get the solution using single pivot. it shows multiple records and its all duplicates.

Regards,
Kalaiselvan R
Love Yourself First....

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-02-29 : 04:50:03

Hi,
I have tried using multiple pivot and not got the results still, Please help me to fix this.

MY QUERY:
---------

SELECT CUSTID,[1],[2],[3],[4],[5],SUM(AMT) FROM
(
SELECT BRANCHID,CUSTID,cast(ACTIVE as TINYINT) ACTIVE,AMT
FROM TABLE
) T
PIVOT (MAX(ACTIVE) for BRANCHID in([1],[2],[3],[4],[5])) as samplepivot2
GROUP BY CUSTID,[1],[2],[3],[4],[5]

RESULTS:
--------

[CUSTID] [1] [2] [3] [4] [5] [AMT]
101 NULL NULL NULL NULL NULL 500
101 NULL NULL NULL NULL 1 500
101 NULL NULL NULL 0 NULL 500
101 NULL NULL 0 NULL NULL 500
101 NULL 1 NULL NULL NULL 500
101 1 NULL NULL NULL NULL 500
102 NULL NULL NULL NULL NULL 500
102 NULL NULL NULL NULL 0 500
102 NULL NULL NULL 1 NULL 500
102 NULL NULL 1 NULL NULL 500
102 NULL 1 NULL NULL NULL 500
102 0 NULL NULL NULL NULL 500

Here the data's were repeating for Active BIT field. Total rows must be 2. And the amount must be summed.
You can check with my 1st post for expected result. Please help me to fix this query.

Regards,
Kalaiselvan R
Love Yourself First....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-29 : 05:32:29

[code]DECLARE @Sample TABLE
(
[ID] INT,
[CUSTID] INT,
[BRANCHID] INT,
[ACTIVE] TINYINT,
[AMT] MONEY
)

INSERT @Sample
VALUES ( 1, 101, 1, 1, 100),
( 2, 101, 2, 1, 100),
( 3, 101, 3, 0, 100),
( 4, 101, 4, 0, 100),
( 5, 101, 5, 1, 100),
( 6, 102, 1, 0, 100),
( 7, 102, 2, 1, 100),
( 8, 102, 3, 1, 100),
( 9, 102, 4, 1, 100),
(10, 102, 5, 0, 100)

-- SwePeso
SELECT s.CUSTID AS [BRANCH/CUSTOMER],
MAX(f.[1]) AS [1],
MAX(f.[2]) AS [2],
MAX(f.[3]) AS [3],
MAX(f.[4]) AS [4],
MAX(f.[5]) AS [5],
SUM(f.AMT) AS TOTALAMOUNT
FROM @Sample AS s
CROSS APPLY (
VALUES (
CASE WHEN BRANCHID = 1 THEN ACTIVE ELSE 0 END,
CASE WHEN BRANCHID = 2 THEN ACTIVE ELSE 0 END,
CASE WHEN BRANCHID = 3 THEN ACTIVE ELSE 0 END,
CASE WHEN BRANCHID = 4 THEN ACTIVE ELSE 0 END,
CASE WHEN BRANCHID = 5 THEN ACTIVE ELSE 0 END,
CASE WHEN ACTIVE = 1 THEN AMT ELSE 0 END
)
) AS f([1], [2], [3], [4], [5], Amt)
GROUP BY s.CUSTID[/code]

N 56°04'39.26"
E 12°55'05.63"

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-03-01 : 06:02:01

Hi,
Its working fine, But in my scenario the BRANCHID's were Dynamic. If its used in PIVOT query I can take those columns in a string and make it with Dynamic query.
But in ur Query its not seem to be done, It works with only mentioning column names. Can you help me by fixing the same query in dynamically without mentioning BRANCHID's..

Regards,
Kalaiselvan R
Love Yourself First....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-01 : 06:44:21

[code]CREATE TABLE #Sample
(
[ID] INT,
[CUSTID] INT,
[BRANCHID] INT,
[ACTIVE] TINYINT,
[AMT] MONEY
)

INSERT #Sample
VALUES ( 1, 101, 1, 1, 100),
( 2, 101, 2, 1, 100),
( 3, 101, 3, 0, 100),
( 4, 101, 4, 0, 100),
( 5, 101, 5, 1, 100),
( 6, 102, 1, 0, 100),
( 7, 102, 2, 1, 100),
( 8, 102, 3, 1, 100),
( 9, 102, 4, 1, 100),
(10, 102, 5, 0, 100)

DECLARE @Agg VARCHAR(MAX)

-- SwePeso
SELECT @Agg = 'SELECT s.CUSTID AS [BRANCH/CUSTOMER]' + d.Agg + ', SUM(f.AMT) AS TOTALAMOUNT'
FROM (
SELECT ', SUM(f.' + QUOTENAME(BRANCHID) + ') AS ' + QUOTENAME(BRANCHID)
FROM #Sample
GROUP BY BRANCHID
ORDER BY BRANCHID
FOR XML PATH('')
) AS d(Agg)

SELECT @Agg += ' FROM #Sample AS s CROSS APPLY ( VALUES (' + STUFF(d.Agg, 1, 2, '') + ', CASE WHEN ACTIVE = 1 THEN AMT ELSE 0 END ) ) AS f('
FROM (
SELECT ', CASE WHEN BRANCHID = ' + CAST(BRANCHID AS VARCHAR(12)) + ' THEN ACTIVE ELSE 0 END'
FROM #Sample
GROUP BY BRANCHID
ORDER BY BRANCHID
FOR XML PATH('')
) AS d(Agg)

SELECT @Agg += STUFF(d.Agg, 1, 2, '') + ', Amt) GROUP BY s.CUSTID ORDER BY s.CUSTID'
FROM (
SELECT ', ' + QUOTENAME(BRANCHID)
FROM #Sample
GROUP BY BRANCHID
ORDER BY BRANCHID
FOR XML PATH('')
) AS d(Agg)

EXEC (@Agg)

DROP TABLE #Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources