Hi I Have A Table ... Is It Possible To Get The Result Mention Below

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

Acc   Plan
121  A
121  A
121  B
131  A
131  B
141  C
141  C
145  D
190  R
200  S

i want result like:

SQL

Acc   Plan
121  A
121  NULL
121  B
131  A
131  B
141  C
141  NULL
145  D
190  R
200  S

Posted 28-Sep-15 3:05am

Tryingtobeageek

Add a Solution

Comments

OriginalGriff 28-Sep-15 9:23am

Why would you want to?
Why not just use DISTINCT and only return the unique rows?

Naveen.Sanagasetti 28-Sep-15 9:31am

Could you please confirm to me, as per your thread header you are asking to get the result. Where you want to show the same.? Is it in gridview or any other datasource controls.?

Tryingtobeageek 29-Sep-15 2:37am

actually in the column Plan if the values are repeated then only for one Acc value should remain same and others should get update as NULL

2 solutions

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)

Naveen.Sanagasetti · Answer 1 · 2015-09-28T03:40:00

As per my understand the post you want to get the result and show it in gridview or any other datasource control, in the above format right.?

If my understanding is correct then do the below steps

Step 1:

Design your gridview column like below

ASP.NET

<asp:TemplateField HeaderText="Material"  HeaderStyle-ForeColor="White">
    <ItemTemplate>
       <asp:Label ID="lblMaterial" runat="server" Text='<%# Bind_Material(Convert.ToString(Eval("Material_Name")))%>'/>
     </ItemTemplate>
</asp:TemplateField>

Step 2:

declare this method in server side of the page

C#

protected string Bind_Material(string sMaterial)
    {
        if (Material == sMaterial)
            {
                    sMaterial = "";
            }
            else
            {
                    Material = sMaterial;
            }
            return sMaterial;
    }

don't do any changes in database result set, keep as it is...

Maciej Los · Answer 2 · 2015-09-29T10:15:00

Have a look at example:

SQL

DECLARE @tmp TABLE(Acc VARCHAR(30), [Plan] VARCHAR(30))

INSERT INTO @tmp (Acc, [Plan])
VALUES('121', 'A'), ('121', 'A'),
('121', 'B'), ('131', 'A'),
('131', 'B'), ('141', 'C'),
('141', 'C'), ('145', 'D'),
('190', 'R'), ('200', 'S')

--1. solution
--===========
SELECT Acc, CASE WHEN ROW_NUMBER() OVER(PARTITION BY Acc, [Plan] ORDER BY [Plan])>1 THEN NULL ELSE [Plan] END AS [Plan]
FROM @tmp

--2.solution
--===========
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--Warning: original values will be overwritten!
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
UPDATE t1 SET t1.[Plan] = NULL
FROM (
    SELECT Acc, [Plan], ROW_NUMBER() OVER(PARTITION BY Acc, [Plan] ORDER BY [Plan]) As RowNo
    FROM @tmp
) AS t1
WHERE t1.RowNo > 1

SELECT *
FROM @tmp

Result:

Acc Plan
121 A
121 NULL
121 B
131 A
131 B
141 C
141 NULL
145 D
190 R
200 S

For further details, please see: Ranking Functions (Transact-SQL)[^]

Hi I Have A Table ... Is It Possible To Get The Result Mention Below

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0