How to get required output from sql query or linq?

Question

0.00/5 (No votes)

See more:

Hi
I have one table whose columns are

SQL

MemberID	int	Unchecked
ParentId	int	Checked
MemberTypeId	int	Unchecked
Name	varchar(50)	Unchecked

and data is

SQL

MemberID     ParentId     MemberTypeId        Name
1              NULL            1               E1
2               1              3               E1 D
3              NULL            1               E2
6              NULL            1               E3
7               6              2               E3 S
8               3              2               E2 S
9               3              3               E2 D
10              6              3               E3 D
11              1              2               E1 S

my output needs to be

XML

<pre lang="SQL">
MemberID     ParentId     MemberTypeId        Name
1              NULL            1               E1
11              1              2               E1 S
2               1              3               E1 D
3              NULL            1               E2
8               3              2               E2 S
9               3              3               E2 D
6              NULL            1               E3
7               6              2               E3 S
10              6              3               E3 D
</pre>

i tried following code but didnt get output

select MemberId,Name from [emp] order by MemberTypeId

I want to clear one more thing according to above example that

first record will be E1 (memberid 1) whose parentid id null followed by its child record i.e. E1 D and E1 S depending upon membertypeid E1 S will be followed by E1 D

i.e output will be

SQL

MemberID     ParentId     MemberTypeId        Name
1              NULL            1               E1
11              1              2               E1 S
2               1              3               E1 D

then next record will be for E2 who have following childs E2 S and E2 D

SQL

MemberID     ParentId     MemberTypeId        Name
3              NULL            1               E2
8               3              2               E2 S
9               3              3               E2 D

and so on....

final output is

SQL

MemberID     ParentId     MemberTypeId        Name
1              NULL            1               E1
11              1              2               E1 S
2               1              3               E1 D
3              NULL            1               E2
8               3              2               E2 S
9               3              3               E2 D
6              NULL            1               E3
7               6              2               E3 S
10              6              3               E3 D

help will be appreciable

regards,
shivani

Posted 13-Oct-14 23:05pm

shivani 2013

Updated 14-Oct-14 1:17am

v6

Add a Solution

5 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)

OriginalGriff · Answer 1 · 2014-10-13T23:12:00

You can't do that simply in SQL: I'd suggest that you try it in your presentation software.
The problem is that the data you want output isn't sorted into any "normal" order:
E1 followed by E2 is fine, but "E1 S" followed by "E1 D" as well just doesn't work because "D" follows "S", and there is no other field in your data which contains the required sort info.

You can do it in C# / VB fairly simply, but in SQL? Not so simple, and prone to errors in future.

_Asif_ · Answer 2 · 2014-10-13T23:42:00

Solution 3

A little bit of trick in Order by can get you desired result. Try this

SQL

SELECT DATA_COLUMN
FROM
(
SELECT 'E1' DATA_COLUMN
UNION ALL SELECT 'E1 D'
UNION ALL SELECT 'E2'
UNION ALL SELECT 'E3'
UNION ALL SELECT 'E3 S'
UNION ALL SELECT 'E2 S'
UNION ALL SELECT 'E2 D'
UNION ALL SELECT 'E3 D'
UNION ALL SELECT 'E1 S'
) A 
ORDER BY REPLACE(DATA_COLUMN, 'S', '+S')

Posted 13-Oct-14 23:42pm

_Asif_

Comments

shivani 2013 14-Oct-14 6:21am

I have updated my quest pls see

_Asif_ 14-Oct-14 7:33am

Try this,
select MemberId, ParentId, MemberTypeId, Name from [emp] order by REPLACE(NAME, 'S', '+S')

jaket-cp · Answer 3 · 2014-10-14T00:26:00

This looks like a case of using recursive CTE:
http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx[^]

SQL

--setup data
declare @Member table (MemberID int, ParentID int, MemberTypeID int, Name varchar(50));
insert into @Member Values(1,NULL,1, 'E1');
insert into @Member Values(2, 1,3, 'E1 D');
insert into @Member Values(3,NULL,1, 'E2');
insert into @Member Values(6,NULL,1, 'E3');
insert into @Member Values(7, 6,2, 'E3 S');
insert into @Member Values(8, 3,2, 'E2 S');
insert into @Member Values(9, 3,3, 'E2 D');
insert into @Member Values(10,6,3, 'E3 D');
insert into @Member Values(11,1,2, 'E1 S');

--CTE recursive
with mem (MemberID, ParentID, MemberTypeID, Name, SortCode)
as(
    select m.MemberID, m.ParentID, m.MemberTypeID, m.Name
    ,convert(varchar(100), m.MemberID) SortCode
    from @Member m
    where m.ParentID is null

    union all

    select m.MemberID, m.ParentID, m.MemberTypeID, m.Name
    ,convert(varchar(100),(SortCode + '.' + convert(varchar(5), m.MemberTypeID) + '-' + right('0000000000'+ rtrim(convert(varchar(5), m.MemberID)), 3))) SortCode
    from mem p
    inner join @Member m
        on p.MemberID = m.ParentID
)
select * from mem
order by SortCode
;

You may have to fiddle with the SortCode.

shivani 2013 · Answer 4 · 2014-10-14T19:38:00

I tried following code:

C#

foreach (var pair in plan.BundlePlanPremiumRates.GroupJoin(plan.BundlePlanPremiumRates,
                             x => x.Member.ID, x => x.Member.ParentId,
                             Tuple.Create).Where(pair => pair.Item1.Member.ParentId == null))
                        {
                            plan.BundlePlanPremiumRates.Add(pair.Item1);
                            plan.BundlePlanPremiumRates.AddRange(pair.Item2.OrderBy(m => m.Member.MemberTypeId));
                        }

it worked

throgh SQLquery its getting complex

aarif moh shaikh · Answer 5 · 2014-10-13T23:28:00

Solution 2

For this type of output you have to Short name field.

For Example

SQL

select Name from [emp] order by name desc

Posted 13-Oct-14 23:28pm

aarif moh shaikh

Comments

Rajesh waran 14-Oct-14 5:36am

Ur Query will provide o/p like this,
E3 S
E3 D
E3
E2 S
E2 D
E2
E1 S
E1 D
E1