How to give ranks to multiple columns data in SQL Server?

I have input table as shown below -

ID  Name q1     q2      q3      q4

1   a    2621   2036    1890    2300

2   b    18000  13000   14000   15000

3   c    100    200     300     400

I want ranking of columns(q1, q2, q3 and q4) data for each row. For example, if I consider last row of above input, then q4 column contains 400 value which is higher than other columns, so rank to q4 column will be 1, q3 rank will be 2, q2 rank will be 3 and q1 rank will be 4.

I am looking for output like -

id  name  q1  q2  q3  q4

1   a     1   3   4   2

2   b     1   4   3   2

3   c     4   3   2   1

There are more than 1 lakh records present in input table.

I have created small SQL script for input table i.e.,

declare @temp table (ID int, Name varchar(10), q1 int, q2 int, q3 int, q4 int)



insert into @temp

select 1, 'a', 2621, 2036, 1890, 2300

union all

select 2, 'b', 18000, 13000, 14000, 15000

union all

select 3, 'c', 100, 200, 300, 400



select * from @temp

Please help me to find efficient way to solve this problem.

asked 32 mins ago

BhatiaAshish

1,321614

1

Have you tried anything?

– Prashant Pimpale
30 mins ago

Have you tried ranking with ... the ranking functions ??

– ta.speot.is
29 mins ago

1

Looks like you want to rank row-wise, rather than column-wise. You'd probably need to pivot, rank, and unpivot.

– HoneyBadger
27 mins ago

Seems like you're trying to get the rank of different columns in a row, not within a set column; aggregate functions work at column level, not row (another reason why you should keep your data normalised). You'll going to need to pivot your data, to normalise it, and then unpivot it back.

– Larnu
27 mins ago

Beat me to it @HoneyBadger :)

– Larnu
27 mins ago

|
show 1 more comment

I have input table as shown below -

ID  Name q1     q2      q3      q4

1   a    2621   2036    1890    2300

2   b    18000  13000   14000   15000

3   c    100    200     300     400

I am looking for output like -

id  name  q1  q2  q3  q4

1   a     1   3   4   2

2   b     1   4   3   2

3   c     4   3   2   1

There are more than 1 lakh records present in input table.

I have created small SQL script for input table i.e.,

declare @temp table (ID int, Name varchar(10), q1 int, q2 int, q3 int, q4 int)



insert into @temp

select 1, 'a', 2621, 2036, 1890, 2300

union all

select 2, 'b', 18000, 13000, 14000, 15000

union all

select 3, 'c', 100, 200, 300, 400



select * from @temp

Please help me to find efficient way to solve this problem.

asked 32 mins ago

BhatiaAshish

1,321614

1

Have you tried anything?

– Prashant Pimpale
30 mins ago

Have you tried ranking with ... the ranking functions ??

– ta.speot.is
29 mins ago

1

Looks like you want to rank row-wise, rather than column-wise. You'd probably need to pivot, rank, and unpivot.

– HoneyBadger
27 mins ago

Seems like you're trying to get the rank of different columns in a row, not within a set column; aggregate functions work at column level, not row (another reason why you should keep your data normalised). You'll going to need to pivot your data, to normalise it, and then unpivot it back.

– Larnu
27 mins ago

Beat me to it @HoneyBadger :)

– Larnu
27 mins ago

|
show 1 more comment

I have input table as shown below -

ID  Name q1     q2      q3      q4

1   a    2621   2036    1890    2300

2   b    18000  13000   14000   15000

3   c    100    200     300     400

I am looking for output like -

id  name  q1  q2  q3  q4

1   a     1   3   4   2

2   b     1   4   3   2

3   c     4   3   2   1

There are more than 1 lakh records present in input table.

I have created small SQL script for input table i.e.,

declare @temp table (ID int, Name varchar(10), q1 int, q2 int, q3 int, q4 int)



insert into @temp

select 1, 'a', 2621, 2036, 1890, 2300

union all

select 2, 'b', 18000, 13000, 14000, 15000

union all

select 3, 'c', 100, 200, 300, 400



select * from @temp

Please help me to find efficient way to solve this problem.

asked 32 mins ago

BhatiaAshish

1,321614

I have input table as shown below -

ID  Name q1     q2      q3      q4

1   a    2621   2036    1890    2300

2   b    18000  13000   14000   15000

3   c    100    200     300     400

I am looking for output like -

id  name  q1  q2  q3  q4

1   a     1   3   4   2

2   b     1   4   3   2

3   c     4   3   2   1

There are more than 1 lakh records present in input table.

I have created small SQL script for input table i.e.,

declare @temp table (ID int, Name varchar(10), q1 int, q2 int, q3 int, q4 int)



insert into @temp

select 1, 'a', 2621, 2036, 1890, 2300

union all

select 2, 'b', 18000, 13000, 14000, 15000

union all

select 3, 'c', 100, 200, 300, 400



select * from @temp

Please help me to find efficient way to solve this problem.

sql-server sql-server-2012

asked 32 mins ago

BhatiaAshish

1,321614

asked 32 mins ago

BhatiaAshish

1,321614

asked 32 mins ago

BhatiaAshish

1,321614

asked 32 mins ago

BhatiaAshish

1,321614

asked 32 mins ago

BhatiaAshish

1,321614

1

Have you tried anything?

– Prashant Pimpale
30 mins ago

Have you tried ranking with ... the ranking functions ??

– ta.speot.is
29 mins ago

1

Looks like you want to rank row-wise, rather than column-wise. You'd probably need to pivot, rank, and unpivot.

– HoneyBadger
27 mins ago

Seems like you're trying to get the rank of different columns in a row, not within a set column; aggregate functions work at column level, not row (another reason why you should keep your data normalised). You'll going to need to pivot your data, to normalise it, and then unpivot it back.

– Larnu
27 mins ago

Beat me to it @HoneyBadger :)

– Larnu
27 mins ago

|
show 1 more comment

1

Have you tried anything?

– Prashant Pimpale
30 mins ago

Have you tried ranking with ... the ranking functions ??

– ta.speot.is
29 mins ago

1

Looks like you want to rank row-wise, rather than column-wise. You'd probably need to pivot, rank, and unpivot.

– HoneyBadger
27 mins ago

Seems like you're trying to get the rank of different columns in a row, not within a set column; aggregate functions work at column level, not row (another reason why you should keep your data normalised). You'll going to need to pivot your data, to normalise it, and then unpivot it back.

– Larnu
27 mins ago

Beat me to it @HoneyBadger :)

– Larnu
27 mins ago

Have you tried anything?

– Prashant Pimpale
30 mins ago

Have you tried ranking with ... the ranking functions ??

– ta.speot.is
29 mins ago

Looks like you want to rank row-wise, rather than column-wise. You'd probably need to pivot, rank, and unpivot.

– HoneyBadger
27 mins ago

Seems like you're trying to get the rank of different columns in a row, not within a set column; aggregate functions work at column level, not row (another reason why you should keep your data normalised). You'll going to need to pivot your data, to normalise it, and then unpivot it back.

– Larnu
27 mins ago

Beat me to it @HoneyBadger :)

– Larnu
27 mins ago

|
show 1 more comment

2 Answers
2

active

oldest

votes

You need UNPIVOT & PIVOT :

SELECT Id, Name, 

       MAX(CASE WHEN qname = 'q1' THEN SEQ END),

       MAX(CASE WHEN qname = 'q2' THEN SEQ END),

       MAX(CASE WHEN qname = 'q3 'THEN SEQ END),

       MAX(CASE WHEN qname = 'q4 'THEN SEQ END)

FROM (SELECT t.*, tt.*, 

             DENSE_RANK() OVER (PARTITION BY t.Name ORDER BY tt.qq DESC) AS SEQ

      FROM @temp t CROSS APPLY

           ( VALUES (q1, 'q1'), (q2, 'q2'), (q3, 'q3'), (q4, 'q4') 

           ) tt(qq, qname)

     ) t

GROUP BY t.Id, t.Name;

answered 21 mins ago

Yogesh Sharma

30.3k51436

Thanks @Yogesh. You crack it really quick.

– BhatiaAshish
23 secs ago

add a comment |

I have tried below one but it is not looking efficient way -

;with cte as(

select

    ID,

    Name,

    convert(xml,

        '<root ' +

            (SELECT ' ' + col+' = "' + CONVERT(VARCHAR(10), ROW_NUMBER() OVER (ORDER BY v DESC)) + '" '

            FROM (

                VALUES (1, 'q1', q1), (2, 'q2', q2), (3, 'q3', q3), (4, 'q4', q4)

                ) AS NewTable(colorder, col, v)

            ORDER BY colorder

            FOR XML PATH('')) +

        '></root>') as xmlData

from @temp t

)

select

    id, name,

    q1 = xmlData.value('(/root/@q1)[1]','int'),

    q2 = xmlData.value('(/root/@q2)[1]','int'),

    q3 = xmlData.value('(/root/@q3)[1]','int'),

    q4 = xmlData.value('(/root/@q4)[1]','int')

from cte

answered 10 mins ago

BhatiaAshish

1,321614

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54515682%2fhow-to-give-ranks-to-multiple-columns-data-in-sql-server%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

You need UNPIVOT & PIVOT :

SELECT Id, Name, 

       MAX(CASE WHEN qname = 'q1' THEN SEQ END),

       MAX(CASE WHEN qname = 'q2' THEN SEQ END),

       MAX(CASE WHEN qname = 'q3 'THEN SEQ END),

       MAX(CASE WHEN qname = 'q4 'THEN SEQ END)

FROM (SELECT t.*, tt.*, 

             DENSE_RANK() OVER (PARTITION BY t.Name ORDER BY tt.qq DESC) AS SEQ

      FROM @temp t CROSS APPLY

           ( VALUES (q1, 'q1'), (q2, 'q2'), (q3, 'q3'), (q4, 'q4') 

           ) tt(qq, qname)

     ) t

GROUP BY t.Id, t.Name;

answered 21 mins ago

Yogesh Sharma

30.3k51436

Thanks @Yogesh. You crack it really quick.

– BhatiaAshish
23 secs ago

add a comment |

You need UNPIVOT & PIVOT :

SELECT Id, Name, 

       MAX(CASE WHEN qname = 'q1' THEN SEQ END),

       MAX(CASE WHEN qname = 'q2' THEN SEQ END),

       MAX(CASE WHEN qname = 'q3 'THEN SEQ END),

       MAX(CASE WHEN qname = 'q4 'THEN SEQ END)

FROM (SELECT t.*, tt.*, 

             DENSE_RANK() OVER (PARTITION BY t.Name ORDER BY tt.qq DESC) AS SEQ

      FROM @temp t CROSS APPLY

           ( VALUES (q1, 'q1'), (q2, 'q2'), (q3, 'q3'), (q4, 'q4') 

           ) tt(qq, qname)

     ) t

GROUP BY t.Id, t.Name;

answered 21 mins ago

Yogesh Sharma

30.3k51436

Thanks @Yogesh. You crack it really quick.

– BhatiaAshish
23 secs ago

add a comment |

You need UNPIVOT & PIVOT :

SELECT Id, Name, 

       MAX(CASE WHEN qname = 'q1' THEN SEQ END),

       MAX(CASE WHEN qname = 'q2' THEN SEQ END),

       MAX(CASE WHEN qname = 'q3 'THEN SEQ END),

       MAX(CASE WHEN qname = 'q4 'THEN SEQ END)

FROM (SELECT t.*, tt.*, 

             DENSE_RANK() OVER (PARTITION BY t.Name ORDER BY tt.qq DESC) AS SEQ

      FROM @temp t CROSS APPLY

           ( VALUES (q1, 'q1'), (q2, 'q2'), (q3, 'q3'), (q4, 'q4') 

           ) tt(qq, qname)

     ) t

GROUP BY t.Id, t.Name;

answered 21 mins ago

Yogesh Sharma

30.3k51436

You need UNPIVOT & PIVOT :

SELECT Id, Name, 

       MAX(CASE WHEN qname = 'q1' THEN SEQ END),

       MAX(CASE WHEN qname = 'q2' THEN SEQ END),

       MAX(CASE WHEN qname = 'q3 'THEN SEQ END),

       MAX(CASE WHEN qname = 'q4 'THEN SEQ END)

FROM (SELECT t.*, tt.*, 

             DENSE_RANK() OVER (PARTITION BY t.Name ORDER BY tt.qq DESC) AS SEQ

      FROM @temp t CROSS APPLY

           ( VALUES (q1, 'q1'), (q2, 'q2'), (q3, 'q3'), (q4, 'q4') 

           ) tt(qq, qname)

     ) t

GROUP BY t.Id, t.Name;

answered 21 mins ago

Yogesh Sharma

30.3k51436

answered 21 mins ago

Yogesh Sharma

30.3k51436

answered 21 mins ago

Yogesh Sharma

30.3k51436

answered 21 mins ago

Yogesh Sharma

30.3k51436

Thanks @Yogesh. You crack it really quick.

– BhatiaAshish
23 secs ago

add a comment |

Thanks @Yogesh. You crack it really quick.

– BhatiaAshish
23 secs ago

Thanks @Yogesh. You crack it really quick.

– BhatiaAshish
23 secs ago

add a comment |

I have tried below one but it is not looking efficient way -

;with cte as(

select

    ID,

    Name,

    convert(xml,

        '<root ' +

            (SELECT ' ' + col+' = "' + CONVERT(VARCHAR(10), ROW_NUMBER() OVER (ORDER BY v DESC)) + '" '

            FROM (

                VALUES (1, 'q1', q1), (2, 'q2', q2), (3, 'q3', q3), (4, 'q4', q4)

                ) AS NewTable(colorder, col, v)

            ORDER BY colorder

            FOR XML PATH('')) +

        '></root>') as xmlData

from @temp t

)

select

    id, name,

    q1 = xmlData.value('(/root/@q1)[1]','int'),

    q2 = xmlData.value('(/root/@q2)[1]','int'),

    q3 = xmlData.value('(/root/@q3)[1]','int'),

    q4 = xmlData.value('(/root/@q4)[1]','int')

from cte

answered 10 mins ago

BhatiaAshish

1,321614

add a comment |

I have tried below one but it is not looking efficient way -

;with cte as(

select

    ID,

    Name,

    convert(xml,

        '<root ' +

            (SELECT ' ' + col+' = "' + CONVERT(VARCHAR(10), ROW_NUMBER() OVER (ORDER BY v DESC)) + '" '

            FROM (

                VALUES (1, 'q1', q1), (2, 'q2', q2), (3, 'q3', q3), (4, 'q4', q4)

                ) AS NewTable(colorder, col, v)

            ORDER BY colorder

            FOR XML PATH('')) +

        '></root>') as xmlData

from @temp t

)

select

    id, name,

    q1 = xmlData.value('(/root/@q1)[1]','int'),

    q2 = xmlData.value('(/root/@q2)[1]','int'),

    q3 = xmlData.value('(/root/@q3)[1]','int'),

    q4 = xmlData.value('(/root/@q4)[1]','int')

from cte

answered 10 mins ago

BhatiaAshish

1,321614

add a comment |

I have tried below one but it is not looking efficient way -

;with cte as(

select

    ID,

    Name,

    convert(xml,

        '<root ' +

            (SELECT ' ' + col+' = "' + CONVERT(VARCHAR(10), ROW_NUMBER() OVER (ORDER BY v DESC)) + '" '

            FROM (

                VALUES (1, 'q1', q1), (2, 'q2', q2), (3, 'q3', q3), (4, 'q4', q4)

                ) AS NewTable(colorder, col, v)

            ORDER BY colorder

            FOR XML PATH('')) +

        '></root>') as xmlData

from @temp t

)

select

    id, name,

    q1 = xmlData.value('(/root/@q1)[1]','int'),

    q2 = xmlData.value('(/root/@q2)[1]','int'),

    q3 = xmlData.value('(/root/@q3)[1]','int'),

    q4 = xmlData.value('(/root/@q4)[1]','int')

from cte

answered 10 mins ago

BhatiaAshish

1,321614

I have tried below one but it is not looking efficient way -

;with cte as(

select

    ID,

    Name,

    convert(xml,

        '<root ' +

            (SELECT ' ' + col+' = "' + CONVERT(VARCHAR(10), ROW_NUMBER() OVER (ORDER BY v DESC)) + '" '

            FROM (

                VALUES (1, 'q1', q1), (2, 'q2', q2), (3, 'q3', q3), (4, 'q4', q4)

                ) AS NewTable(colorder, col, v)

            ORDER BY colorder

            FOR XML PATH('')) +

        '></root>') as xmlData

from @temp t

)

select

    id, name,

    q1 = xmlData.value('(/root/@q1)[1]','int'),

    q2 = xmlData.value('(/root/@q2)[1]','int'),

    q3 = xmlData.value('(/root/@q3)[1]','int'),

    q4 = xmlData.value('(/root/@q4)[1]','int')

from cte

answered 10 mins ago

BhatiaAshish

1,321614

answered 10 mins ago

BhatiaAshish

1,321614

answered 10 mins ago

BhatiaAshish

1,321614

answered 10 mins ago

BhatiaAshish

1,321614

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

4bMYG TDiU HfDn,AUos6f,mMk3Qf7

搜尋此網誌

Ndtyhij