Please find the tables below

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

I have table like this
ID Name
1 Naveen
2 Naveen
3 Naveen
4 Praveen
5 Praveen

I need result mentioned below, If required u can create new columns as col1,col2 and Col3 or you u use select statement only by using case statement.

ID Name <>Col1<>Col2<>Col3
1 Naveen <>1<>null<>null
2 Naveen <>null<>2<>null
3 Naveen <>null<>null<>3
4 Praveen <>1<>null<>null
5 Praveen <>null<>2<>null

Edit - content of solution posted by OP

ID Name      Col1     Col2	    Col3
1  Naveen    1	      null	         null
2  Naveen    null     2             null
3  Naveen    null     null          3
4  Praveen   1        null          null
5  Praveen   null     2             null

Yes exact output i required.... Please share query of that above ssolution

Posted 15-Jul-15 21:22pm

Tryingtobeageek

Updated 16-Jul-15 2:23am

v6

Add a Solution

Comments

gregorio89 16-Jul-15 3:32am

you can have only 3 rows for each name?

Tryingtobeageek 16-Jul-15 3:57am

no for col1 is '1' col2 is '-' and col3 is '-' for ID 1
same for ID 2 col1 is '-' col2 is '2' and col3 is '-'

gregorio89 16-Jul-15 4:06am

so the question was:
how many rows can you have for same name?
i see 3 rows for name Naveen, 2 for praveen.
you have always 3 rows for each name?

Tryingtobeageek 16-Jul-15 5:07am

and consider col1 , col2,col3 as a separate columns in tables

Tryingtobeageek 16-Jul-15 5:09am

under those columns need result

ID Name Col1 Col2 Col3
1 Naveen 1 - -
2 Naveen - 2 -
3 Naveen - - 3

Tryingtobeageek 16-Jul-15 5:09am

Please help me out. . .

CHill60 16-Jul-15 6:46am

We would like to help you out, but your question is not at all clear. What are the "rules" for an Id appearing in col1, col2 or col3?

Tryingtobeageek 16-Jul-15 7:27am

For Example in a table called 'Employee' 5 columns called
: ID ,Name,Type1,Type,Type2 are there

In Type1=1 where id=1
Type2=null where id=2
Type3=null where id=3

In Type1=null where id=1
Type2=2 where id=2
Type3=null where id=3

In Type1=null where id=1
Type2=null where id=2
Type3=3 where id=3

CHill60 16-Jul-15 7:29am

Do you want to share the table you actually are interested in with some sample data - like you did in your original post (but use <pre> tags to format it). Use the Improve question link to update your original post

Tryingtobeageek 16-Jul-15 7:48am

Hi sir... is there any option to send excel attachment so that i can share my result sheet

CHill60 16-Jul-15 7:50am

Other members have taken screenshots and posted them on imgur or similar - or you could just put a sample into your question above.

ZurdoDev 16-Jul-15 7:20am

What table does col1, col2, and col3 come from?

Tryingtobeageek 16-Jul-15 7:28am

For Example in a table called 'Employee' 5 columns called
: ID ,Name,Type1,Type,Type2 are there

note : All type1,type2 and type3 are null

In Type1=1 where id=1
Type2=null where id=2
Type3=null where id=3

In Type1=null where id=1
Type2=2 where id=2
Type3=null where id=3

In Type1=null where id=1
Type2=null where id=2
Type3=3 where id=3

ZurdoDev 16-Jul-15 7:30am

This still does not make any sense. It's not clear what you want.

Tryingtobeageek 16-Jul-15 7:48am

Hi sir... is there any option to send excel attachment so that i can share my result sheet

ZurdoDev 16-Jul-15 7:49am

No. Just click improve question and add some sample data there.

Tryingtobeageek 16-Jul-15 7:53am

just now i have altered my question have u understood that...??

ZurdoDev 16-Jul-15 8:18am

No. Why does the record 1 Naveen have 1, NULL, NULL? What are the rules? You need to describe in English what the pattern or rules are.

CHill60 16-Jul-15 8:20am

No - you have just presented your expected results again. We need to see your sample data and the rules that we need to apply to get those expected results

gregorio89 17-Jul-15 6:59am

solution 2 work only with this records.
do you have more records?

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)

Ben J. Boyle · Accepted Answer · 2015-07-16T06:08:00

With your very small sample set and lack of rules, this will get you what you want.

SQL

select [id], name,
        case when [id] % 3 = 1 then 1 end as col1,
        case when [id] % 3 = 2 then 2 end as col2,
        case when [id] % 3 = 0 then 3 end as col3
from names

If you need more Coln columns, or there are more than three of each name, then it won't work.

If you;re trying to say that "ID is the nth instance of Name" then you might need to parse the info into a temp table and look at pivoting the results to get the coln.