Using the crosstab() function while appending to the column names & using boolean logic

Question

I'm using Postgresl 9.2

I need a crosstab table created from this:

select id, imp from sg_imp_id (There are A LOT more rows than this)

  id   |  imp  |
-------+-------+
  1    |  111  |
  2    |  111  | 
  2    |  121  | 
  2    |  122  |
  3    |  131  |
  4    |  154  |
  ....    ....

Like this:

    id   | x111 | x121 | x122 | x131 | x154 |
---------+------+------+------+------+------+
    1    |   1  |   0  |  0   |  0   |  0   |
    2    |   1  |   1  |  1   |  0   |  0   |
    3    |   0  |   0  |  0   |  1   |  0   |
    4    |   0  |   0  |  0   |  0   |  1   |

With a column for every imp row and whenever an id has that imp number, to place a 1. If it doesn't have that imp number then a 0 should be in that spot. I have very limited knowledge of the crosstab() function. There are currently very many different rows of "x111,x112,x113" values so using the case clause won't really be probable.

So you just want to prefix the column names that `crosstab` produces? Have you look at the `crosstab` source? — mu is too short, Nov 07 '13 at 21:38
I want to prefix the column names but I don't even know how to get my results as their own column using `crosstab()` — precose, Nov 08 '13 at 14:15
But you can get `crosstab()` to give you `111`, `121`, ... as column names? — mu is too short, Nov 08 '13 at 21:24

score 2 · Answer 1 · answered Nov 05 '13 at 20:06

2

Not sure about crosstab() function, but you can always pivot manually:

select
    id,
    max(case when imp = 111 then 1 else 0 end) as x111,
    max(case when imp = 121 then 1 else 0 end) as x121,
    max(case when imp = 122 then 1 else 0 end) as x122,
    max(case when imp = 131 then 1 else 0 end) as x131,
    max(case when imp = 154 then 1 else 0 end) as x154
from Table1
group by id

sql fiddle demo

answered Nov 05 '13 at 20:06

Roman Pekar

107,110
28
195
197

The only problem with using this method is there are more than just the rows I added. There are tons of x122,x123,x124 etc.. There are so many that manually writing them all out is virtually impossible. – precose Nov 05 '13 at 20:20
1

Afaik there's no way to return dynamic number of columns in Postgresql. And, actually, it's not good idea.SQL is designed to have fixed number of columns and dynamic number of rows, it's usually better to pivot on the client side – Roman Pekar Nov 05 '13 at 20:36

score 0 · Answer 2 · answered Nov 12 '13 at 13:15

Please Try it , It is some useful to you

DECLARE @SQL VARCHAR(MAX)
SELECT  @SQL = ISNULL(@SQL, '') + ',
(
SELECT  
 id 
FROM  stacky.dbo.crosstab t' + Rownum + ' 
WHERE   t' + Rownum + '.id = t.id   AND     t' + Rownum + '.imp = ''' + imp + ''' 
) 
[x' + imp + ']'
FROM ( SELECT  imp, CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY imp)) [RowNum] FROM   stacky.dbo.crosstab  GROUP BY imp) d



SET @SQL = 'SELECT id ' + @SQL + ' FROM (SELECT DISTINCT id FROM stacky.dbo.crosstab) t'
EXEC (@SQL)

Please Change DAtabase name and (id int),(imp varchar(20)) type Other then use casting

Is this for SQL Server or Postgresql? – precose Nov 12 '13 at 15:14 — precose, Nov 12 '13 at 15:14

Using the crosstab() function while appending to the column names & using boolean logic

2 Answers2