How can I pivot a Column or do the following on SQL Server:

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Ent_ID Dest_ID Bank_ID Post_ID
12---- 800---- 1------ 2------
13---- 900---- 1------ 2------
14---- 800---- 1------ 9------
15---- 800---- 1------ 10-----

I'm trying to display this in such a way using SQL so that Ent_ID (12, 13) shows up as one row because their Bank_ID and Post_ID are same.

My ultimate goal is to return this in Datatable for winforms and translate it to datagridview where I have Dest_ID data as checkbox header column.

Bank_ID--Post_ID-- Dest_ID_800--Dest_ID_900
1--------- 2--------- 800------------900
1--------- 9--------- 800------------
1--------- 10------------------------900

Posted 8-Aug-14 11:18am

Bhis

Add a Solution

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 · Accepted Answer · 2014-08-10T15:10:00

Hi,

You can create a static or dynamic pivot query to transform the data.

Here's an example of the static pivot query:

SQL

DECLARE @DataTable TABLE (Ent_ID INT, Dest_ID INT, Bank_ID INT, Post_ID INT);

INSERT INTO @DataTable (Ent_ID, Dest_ID, Bank_ID, Post_ID)
   VALUES (12, 800, 1, 2),
          (13, 900, 1, 2),
          (14, 800, 1, 9),
          (15, 900, 1, 10);

SELECT Bank_ID, Post_ID, [800] AS Dest_ID_800, [900] AS Dest_ID_900
FROM
   (SELECT Dest_ID, Bank_ID, Post_ID
   FROM @DataTable) AS SourceTable
PIVOT (MAX(Dest_ID) FOR Dest_ID IN
   ([800], [900])
) AS PivotTable;

Result:

XML

Bank_ID	Post_ID	Dest_ID_800	Dest_ID_900
1	2	800		900
1	9	800		NULL
1	10	NULL		900

A static query is good if the number of columns (Dest_ID_800, Dest_ID_900, etc.) is fixed. If you want dynamic number of columns (based on the data in Dest_ID column), you can write a dynamic pivot query, i.e. you need to rewrite the static query to be dynamically written.

If you want to write a dynamic pivot query, please take a look here:
1. Dynamic PIVOT in Sql Server[^]
2. An example of a dynamic pivot query[^]