0

I have a table with hundreds of columns. I need to take the result of every column (except one) and put them into an array and bring back the rest of the results. Here it was the table looks like:

ID          x123     x124    x125    x126  ......
2323343     0        0       0       1
3434566     1        1       1       0
3434342     1        1       0       0 
3366577     0        1       1       1
....        ....     ....    ....    ....

This table continues on for a while. Basically I need all of the x# column's results brought back in an array with the rest of the tables results (except for the ID column). So that my results would look like:

array            x123     x124    x125    x126  ......
{0,0,0,1,...}    0        0       0       1
{1,1,1,0,...}    1        1       1       0
{1,1,0,0,...}    1        1       0       0 
{0,1,1,1,...}    0        1       1       1
....             ....     ....    ....    ....

my current SQL statement is something like this:

select * from mffcu.crosstab_183

I figure this would take a function of some sort to build a table with these results and that is fine. I really don't know where to begin with getting EVERY column and EVERY record to be thrown into an array right now without NAMING every single column (there are so many). Any swing in the right direction would help greatfully.

precose
  • 614
  • 1
  • 13
  • 36

3 Answers3

2

If the format of your table is as simple and strict as it seems (the first column consists of 7 digits), you could resort to a very simple trick:

SELECT string_to_array(right(left(t::text, -1), -9), ',')
FROM   mffcu.crosstab_183 t;

That's all.
left() and right() require PostgreSQL 9.1 or above. For older versions:

SELECT string_to_array(substring(rtrim(t::text, ')'), 10), ',')
FROM   mffcu.crosstab_183 t;

Explain

Every type can be cast to text in Postgres, that includes composite and row types. So

  1. Cast the whole row to text.
  2. Remove enclosing parentheses and the first column - in this case identified by length.
  3. Convert the result to an array with string_to_array().
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

I think you'll need to select all, as you are, then set the first field in each row of the result array to be an array of the remaining results in that row. It's not pretty but it works.

To my knowledge there is no way of excluding a column from a select statement. You either need to SELECT * or name each column to include.

How this is done depends on the programming language you're using to process the data returned from the SELECT.

Intermernet
  • 18,604
  • 4
  • 49
  • 61
  • "I think you'll need to select all, as you are, then set the first field in each row of the result array to be an array of the remaining results in that row. It's not pretty but it works." Can you send me in the right direction on how to achieve this? Thanks! @Intermernet – precose May 21 '13 at 13:41
  • 1
    Hi, I'm not sure what language you're using. If you're trying to do it in PLSQL I'd suggest a stored procedure that loops through each row, removes the first field, creates a VARRAY of the remaining fields and then returns the row with the VARRAY (or a TABLE, not to be confused with regular SQL tables) in the place of the original first field, but I'm not too good on PLSQL and you'd probably find this easier using something like Python, PHP or Perl. See http://docs.oracle.com/cd/B10500_01/appdev.920/a96624/05_colls.htm – Intermernet May 21 '13 at 13:54
0

I would recommend to look at temp tables, shema info and run-time sql the code below may give you some ideas... (the code is done in sql server dialect. some adjustments might be necessery for postgree..)

CREATE TABLE #Result(Id int, ColumnId VARCHAR(10), Value TINYINT);

DECLARE db_cursor CURSOR FOR  
SELECT 'INSERT INTO #Result SELECT ID, ''' + COLUMN_NAME +''', '+ COLUMN_NAME + ' FROM ManyColumnsTable' --' 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'ManyColumnsTable' AND COLUMN_NAME LIKE 'x%'

DECLARE @command NVARCHAR(80)

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @command  

WHILE @@FETCH_STATUS = 0   
BEGIN   
       PRINT @command

       EXECUTE sp_executesql @command 

       FETCH NEXT FROM db_cursor INTO @command   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

SELECT * FROM #Result --WHERE ColumnId = 'x102'

DROP TABLE #Result
Yaugen Vlasau
  • 2,148
  • 1
  • 17
  • 38