0

I have a requirement to convert distinct row values into columns. The following table is available with Source, Priority and Count.

|Source |Priority|Count|
|Email  |P4      |19   |
|Phone  |P3      |27   |
|Email  |P3      |53   |
|Outlook|P4      |77   |
|Email  |P2      |12   |

Now I have to generate the output in below tabular format in a JSP page. Can this output table be generated using SQL?

|Source |P4|P3|P2|P1|
|Email  |19|53|12|0 |
|Phone  |0 |27|0 |0 |
|Outlook|77|0 |0 |0 |
Cray
  • 2,774
  • 7
  • 22
  • 32
Rekha Bhalla
  • 1
  • 1
  • 1
    Hi, and welcome to SO. First, could you insert the tables (as they are quite small) as tables to your question. Second, you should show us what you have tried already. So, paste some code. What SQL language are you using? – Juho Rutila Jul 09 '20 at 07:48

2 Answers2

2

Here you can find the solution for your question using PIVOT:

create table #Table1 (Sources varchar(10), Priority varchar (5), Counts int)

insert into #Table1 values ('Email','P4',19)
insert into #Table1 values ('Phone','P3',27)
insert into #Table1 values ('Email','P3',53)
insert into #Table1 values ('Outlook','P4',77)
insert into #Table1 values ('Email','P2',12)

SELECT Sources,ISNULL([P4],0) AS P4,ISNULL([P3],0) AS P3,ISNULL([P2],0) AS P2,ISNULL([P1],0) AS P1
FROM
(
    SELECT Sources,
           Priority,
           Counts
    FROM #Table1
) AS SourceTable PIVOT(SUM(Counts) FOR Priority IN([P4],[P3],[P2],[P1])) AS PivotTable;

I hope it will be useful for you! Bye!

Alessandro Bardini
  • 122
  • 4
0

You can find a good article here: Efficiently convert rows to columns in sql server

You can try in this way, i'm pretty sure you can solve your problem usign this guide, See PIVOT TABLE

Alessandro Bardini
  • 122
  • 4