How to Select Columns as Rows

how to select columns as rows?

This should work for any table, but in my example I just create a test one. You need to set the table name within @YourTableName. Also, you need to set @YourTableWhere to limit the results to one row, otherwise the output looks strange with multiple rows mixed together.

try this:

BEGIN TRY
CREATE TABLE YourTestTable
(RowID       int primary key not null identity(1,1)
,col1        int null
,col2        varchar(30)
,col3        varchar(20)
,col4        money
,StatusValue char(1)
,xyz_123     int
)
INSERT INTO YourTestTable (col1,col2,col3,col4,StatusValue,xyz_123) VALUES (1234,'wow wee!','this is a long test!',1234.56,'A',98765)
INSERT INTO YourTestTable (col1,col2,col3,col4,StatusValue,xyz_123) VALUES (543,'oh no!','short test',0,'I',12)

END TRY BEGIN CATCH END CATCH

select * from YourTestTable

DECLARE @YourTableName   varchar(1000)
DECLARE @YourTableWhere  varchar(1000)
DECLARE @YourQuery       varchar(max)

SET @YourTableName='YourTestTable'
set @YourTableWhere='y.RowID=1'

SELECT
    @YourQuery = STUFF(
                       (SELECT
                            ' UNION '
                            + 'SELECT '''+COLUMN_NAME+''', CONVERT(varchar(max),'+COLUMN_NAME+') FROM '+@YourTableName+' y'+ISNULL('  WHERE '+@YourTableWhere,'')
                            FROM INFORMATION_SCHEMA.COLUMNS
                            WHERE table_name = @YourTableName
                            FOR XML PATH('')
                       ), 1, 7, ''
                      )

PRINT @YourQuery  

EXEC (@YourQuery)

OUTPUT:

RowID       col1        col2                           col3                 col4                  StatusValue xyz_123
----------- ----------- ------------------------------ -------------------- --------------------- ----------- -----------
1           1234        wow wee!                       this is a long test! 1234.56               A           98765
2           543         oh no!                         short test           0.00                  I           12

SELECT 'RowID', CONVERT(varchar(max),RowID) FROM YourTestTable y  WHERE y.RowID=1 UNION SELECT 'col1', CONVERT(varchar(max),col1) FROM YourTestTable y  WHERE y.RowID=1 UNION SELECT 'col2', CONVERT(varchar(max),col2) FROM YourTestTable y  WHERE y.RowID=1 UNION SELECT 'col3', CONVERT(varchar(max),col3) FROM YourTestTable y  WHERE y.RowID=1 UNION SELECT 'col4', CONVERT(varchar(max),col4) FROM YourTestTable y  WHERE y.RowID=1 UNION SELECT 'StatusValue', CONVERT(varchar(max),StatusValue) FROM YourTestTable y  WHERE y.RowID=1 UNION SELECT 'xyz_123', CONVERT(varchar(max),xyz_123) FROM YourTestTable y  WHERE y.RowID=1

----------- ------------------------
col1        1234
col2        wow wee!
col3        this is a long test!
col4        1234.56
RowID       1
StatusValue A
xyz_123     98765

EDIT

For SQL Server 2000 compatibility, you should be able to replace varchar(max) with varchar(8000) and use this in place of the SELECT @YourQuery query from the code above:

SELECT
    @YourQuery=ISNULL(@YourQuery+' UNION ','')
        + 'SELECT '''+COLUMN_NAME+''', CONVERT(varchar(max),'+COLUMN_NAME+') FROM '+@YourTableName+' y'+ISNULL('  WHERE '+@YourTableWhere,'')
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = @YourTableName

SQL Server : Columns to Rows

You can use the UNPIVOT function to convert the columns into rows:

select id, entityId,
  indicatorname,
  indicatorvalue
from yourtable
unpivot
(
  indicatorvalue
  for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;

Note, the datatypes of the columns you are unpivoting must be the same so you might have to convert the datatypes prior to applying the unpivot.

You could also use CROSS APPLY with UNION ALL to convert the columns:

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  select 'Indicator1', Indicator1 union all
  select 'Indicator2', Indicator2 union all
  select 'Indicator3', Indicator3 union all
  select 'Indicator4', Indicator4 
) c (indicatorname, indicatorvalue);

Depending on your version of SQL Server you could even use CROSS APPLY with the VALUES clause:

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  values
  ('Indicator1', Indicator1),
  ('Indicator2', Indicator2),
  ('Indicator3', Indicator3),
  ('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);

Finally, if you have 150 columns to unpivot and you don't want to hard-code the entire query, then you could generate the sql statement using dynamic SQL:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'yourtable' and
                 C.column_name like 'Indicator%'
           for xml path('')), 1, 1, '')

set @query 
  = 'select id, entityId,
        indicatorname,
        indicatorvalue
     from yourtable
     unpivot
     (
        indicatorvalue
        for indicatorname in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

Oracle SQL: How to convert one column of Select to rows

A simple method -- if your data is not too large -- is just to use union all. Your description makes it sound like you want this:

select col1, col2, col5
from t
where col2 is not null
union all
select col1, col3, col5
from t
where col2 is not null
union all
select col1, col4, col5
from t
where col2 is not null;

Hmmm, or if you just want the distinct values in col2:

select distinct col2
from t;

Pandas: Select columns based on row values

Use first DataFrame.loc for select all rows (:) by mask compred selected row Region by another loc:

df = df.loc[:, df.loc['Region'] == 'Nord']
print (df)
           A     D     E
Region  Nord  Nord  Nord
value    2.3   0.5   1.3
value2    20   123   200

Better is crated MultiIndex by first row with original columns, then is possible select by DataFrame.xs:

df.columns = [df.columns, df.iloc[0]]
df = df.iloc[1:].rename_axis((None, None), axis=1)
print (df)
          A    B    C    D    E
       Nord  Süd West Nord Nord
value   2.3  1.2  4.2  0.5  1.3
value2   20  400   30  123  200

---

print (df.xs('Nord', axis=1, level=1))
          A    D    E
value   2.3  0.5  1.3
value2   20  123  200

print (df.xs('Nord', axis=1, level=1, drop_level=False))
          A    D    E
       Nord Nord Nord
value   2.3  0.5  1.3
value2   20  123  200

Efficiently convert rows to columns in sql server

There are several ways that you can transform data from multiple rows into columns.

Using PIVOT

In SQL Server you can use the PIVOT function to transform the data from rows to columns:

select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
  select value, columnname
  from yourtable
) d
pivot
(
  max(value)
  for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;

See Demo.

Pivot with unknown number of columnnames

If you have an unknown number of columnnames that you want to transpose, then you can use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from yourtable
                    group by ColumnName, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

exec sp_executesql @query;

See Demo.

Using an aggregate function

If you do not want to use the PIVOT function, then you can use an aggregate function with a CASE expression:

select
  max(case when columnname = 'FirstName' then value end) Firstname,
  max(case when columnname = 'Amount' then value end) Amount,
  max(case when columnname = 'PostalCode' then value end) PostalCode,
  max(case when columnname = 'LastName' then value end) LastName,
  max(case when columnname = 'AccountNumber' then value end) AccountNumber
from yourtable

See Demo.

Using multiple joins

This could also be completed using multiple joins, but you will need some column to associate each of the rows which you do not have in your sample data. But the basic syntax would be:

select fn.value as FirstName,
  a.value as Amount,
  pc.value as PostalCode,
  ln.value as LastName,
  an.value as AccountNumber
from yourtable fn
left join yourtable a
  on fn.somecol = a.somecol
  and a.columnname = 'Amount'
left join yourtable pc
  on fn.somecol = pc.somecol
  and pc.columnname = 'PostalCode'
left join yourtable ln
  on fn.somecol = ln.somecol
  and ln.columnname = 'LastName'
left join yourtable an
  on fn.somecol = an.somecol
  and an.columnname = 'AccountNumber'
where fn.columnname = 'Firstname'

Select columns based on row values in pandas and use the column indexes for subsetting

Use:

df1 = df.iloc[:, 3:]
fin = df1.columns[(df1.iloc[0] == 1) & (df.iloc[1, 3:] == 1)]
print (fin)
Index([3], dtype='object')

Original solution:

out = df.columns[3:][df.iloc[0, 3:] == 1]
s = df.loc[1, out]

fin = s.index[s == 1]
print (fin)
Index([3], dtype='object')

Select specific rows and columns from an SQL database

Replace the wildcard symbol * with the column names you want to retrieve.

But please read up the documentation on SQL standard. It is very unlikely you need 1.000 columns in a table.

---

Related Topics