CODEWITHSUNDEEP
sqlreporting-services
Ice
Ice

Reputation: 459

Create horizontal columns

I have a table and the format is below:

Staff      Client   Problems Status
    1        101      a        1
    1        101      b        0
    1        101      e        1
    2        102      g        0
    2        102      k        1

I need to create a report based on it and the format is as bellows. 

Staff        Client   Problem1 Status  Problem2  Status Problem3 Status.....
1            101         a       1           b       0      e      1
2            102         g       0           k       1

Update:

With SqlZim's help, I get the result as below:

Staff      Client     Category  Problems Status
        1        101    Problem1    a        1
        1        101    Problem2    b        0
        1        101    Problem3    e        1
        2        102    Problem1    g        0
        2        102    Problem2    k        1

I create a matrix SSRS report and set up Category as column groups and Client as Row Groups and Problems as values.

I got an error.

Can you help me with it? Thanks!

Upvotes: 1

Views: 64

Answers (1)

SqlZim
SqlZim

Reputation: 38023

In SQL Server:

You can use row_number() to number the rows, and concatenate 'Problems'

select 
    Staff
  , Client
  , 'Problem'+convert(varchar(10),
      row_number() over (partition by Staff, Client order by Problems)
      ) as Category
  , Problems
  , Status
from t

In SQL Server 2012+ you can use concat() to concatenate the values instead of having to explicitly convert the row_number() to varchar():

select 
    Staff
  , Client
  , concat('Problem',
      row_number() over (partition by Staff, Client order by Problems)
      ) as Category
  , Problems
  , Status
from t

rextester demo: http://rextester.com/ULH84019

returns:

+-------+--------+----------+----------+--------+
| Staff | Client | Category | Problems | Status |
+-------+--------+----------+----------+--------+
|     1 |    101 | Problem1 | a        |      1 |
|     1 |    101 | Problem2 | b        |      0 |
|     1 |    101 | Problem3 | e        |      1 |
|     2 |    102 | Problem1 | g        |      0 |
|     2 |    102 | Problem2 | k        |      1 |
+-------+--------+----------+----------+--------+

Upvotes: 2

Related Questions