Transpose in SQL

Transpose in SQL

Post by Ng K C Pa » Sat, 17 Oct 1998 04:00:00



Is it possible to do the Transpose function using SQL like under Excel's
Paste Special - change columns of data to rows, and vice versa?
 
 
 

Transpose in SQL

Post by Reid La » Sat, 24 Oct 1998 04:00:00


Not possible only with SQL.  You may utilize PL/SQL as well.


Quote:> Is it possible to do the Transpose function using SQL like under Excel's
> Paste Special - change columns of data to rows, and vice versa?

--
Best Regards,


Certified Oracle7 DBA (OCP)
================================================
The above opinions are mine and do not represent
any official standpoints of my employer

 
 
 

Transpose in SQL

Post by Brad » Mon, 26 Oct 1998 03:00:00


It is possible in Oracle by using the proprietary DECODE statement. I think
this can be done in SQLServer using the CASE statement.  The Decode
statement looks like this.

SELECT
  (DECODE(TagName,'Fermentation volume',Tag_Value,NULL) "Fermentation
volume",
  (DECODE(TagName,'Fermentation pH',Tag_Value,NULL) "Fermentation pH",
FROM
  Fermentation

in SQL Server this I guess this would be

SELECT
  TagName "Fermentation volume" =
        CASE
            WHEN TagName IS NULL THEN NULL
            WHEN TagName IS 'Fermentation volume' THEN Tag_Value
        END,
  TagName "Fermentation pH" =
        CASE
            WHEN TagName IS NULL THEN NULL
            WHEN TagName IS 'Fermentation pH'THEN Tag_Value
        END
FROM
  Fermentation

NULL Values can be stripped out using aggregate functions, and by nesting
the SQL statement as:

SELECT Fermentation volume" ,"Fermentation pH"  FROM
 (
 SELECT DISTINCT
  AVG(DECODE(TagName,'Fermentation volume',Tag_Value,NULL)) "Fermentation
volume",
  AVG(DECODE(TagName,'Fermentation pH',Tag_Value,NULL)) "Fermentation pH",
FROM
  Fermentation
)
WHERE
  "Fermentation volume" IS NOT NULL OR
  "Fermentation pH" IS NOT NULL ;


>Not possible only with SQL.  You may utilize PL/SQL as well.


>> Is it possible to do the Transpose function using SQL like under Excel's
>> Paste Special - change columns of data to rows, and vice versa?

>--
>Best Regards,


>Certified Oracle7 DBA (OCP)
>================================================
>The above opinions are mine and do not represent
>any official standpoints of my employer

 
 
 

Transpose in SQL

Post by Hobar » Tue, 27 Oct 1998 04:00:00


In Sybase, the CASE keyword is for scripting use. It can't be embedded in an
SQL Statement.

You can transpose rows to columns and visa versa in a single Sybase SQL
statement by using Sybase's characteristic functions and add'l functions. These
include (SIGN, ABS, ISNULL, SUBSTRING, etc)

How you combine them depends on what you're trying to do. There are excellent
books on the topic published by the SQL Forum Press.

 
 
 

Transpose in SQL

Post by jmor » Fri, 30 Oct 1998 04:00:00


I have used CASE statements embedded in Sybase SQL for many on the fly
recodes.
The CASE methods mentioned earlier  or something similar should work with
the newer versions of Sybase.
At least try them out before blindly following Hobarts 'Nice' RTFM
suggestion.

Possibly Hobart needs to upgrade or read some books on advanced SQL himself
 ;-)
--

John



Quote:> In Sybase, the CASE keyword is for scripting use. It can't be embedded in
an
> SQL Statement.

> You can transpose rows to columns and visa versa in a single Sybase SQL
> statement by using Sybase's characteristic functions and add'l functions.
These
> include (SIGN, ABS, ISNULL, SUBSTRING, etc)

> How you combine them depends on what you're trying to do. There are
excellent
> books on the topic published by the SQL Forum Press.

 
 
 

Transpose in SQL

Post by BPMargoli » Fri, 20 Nov 1998 04:00:00


Try using the ISNULL function. Something like:

select count(ISNULL(tab_b.outlet_id,'o1')), sales_id,pack_id
from tab_a, tab_b
where tab_a.outlet_id *= tab_b.outlet_id
group by sales_id,pack_id

 
 
 

Transpose in SQL

Post by gus » Fri, 20 Nov 1998 04:00:00



> thks for the reply, here the problem is due to the " group by " clause. I had
> try all these suggestion but bcoz of the group by that makes the the count
> based on the group by. Does anyone try to group by with exception case like
> this, group the record but count the nulls records as well?

> Thanks
> sam




> > > Hi

> > > Appreciate if anyone could help and gives ideas

> > > I am writing a select statement where I need the return value of a total
> count
> > > records that includes null values but with group by statement, e.g

> > > tab_a (sales_id, outlet_id)
> > > tab_b (outlet_id, pack_id)

> > > tab_a
> > > sales_id  outlet_id
> > > s1        o1
> > > s2        o2
> > > s3        o3

> > > tab_b
> > > outlet_id     pack_id
> > > o1            p1
> > > o2            p2

> > > the sql statement is like this
> > > select count(tab_b.outlet_id), sales_id,pack_id
> > > from tab_a, tab_b
> > > where tab_a.outlet_id *= tab_b.outlet_id
> > > group by sales_id,pack_id

> > > the return value of the count does not includes the null values eventhough
> the
> > > outer join due to the group by clause. I need the count to includes the null
> > > values as well

> > > Anyone could gives me some ideas how to go about it?

> > > sam

[Much Snipped]

Duh!, I feel a bit dumb, I shoulf have noticed that this was posted to a
number of groups. I have no idea of how Oracle behaves in this case, and
the answer I suggested earlier is based purely on my Sybase experience.
MS SQL Server will 99% probably behave the same.

So, why didn't you tell use that you wanted the Count to count the
number of records in the resultset *before* the group by, and not the
number of records in each group.

So, now I can say that the moment you include a "group by" that all
aggregate functions (Sum, Min, Max, Count, Avg) will be applied to each
group. There is no way around that.

Now, the question I have, is why then do you need the group by. The
example above is simplistic, but it seems that you may get what you want
with just:

    select count(*), sales_id,pack_id
    from tab_a, tab_b
    where tab_a.outlet_id *= tab_b.outlet_id

The Count (*) value will mow be the total number of rows returned, and
this would be the same value in every record. In your example now you
would get:

      sales_id pack_id
----- -------- -------
3     s1       p1
3     s2       p2
3     s3       NULL

If you instead used Count (tab_b.outlet_id) you would get:

      sales_id pack_id
----- -------- -------
2     s1       p1
2     s2       p2
2     s3       NULL

Otherwise, I have again misunderstood what you need ;-)

cheers

gus

 
 
 

1. How to Transpose a SQL Table (Matrix Transpose)?

I have source table with an unknown number of columns and
one row, like this:
Col1, Col2, Col3, Col4, ...
-------------------------
Val1, Val2, Val3, Val4, ...

I'd like to build a query / function / sp to transpose it
into a table with exactly two columns and an unknown
number of rows, like this:

Name, Value
-----------
Col1, Val1
Col2, Val2
Col3, Val3
Col4, Val4
... , ...

How do I do it without hardcoding column names? (Needs to
be reused on multiple tables)

In Excel, this is the TRANSPOSE function. In Access,
there's something called PIVOT but I can't find anything
similar in SQL Server. What gives??

Thanks,
-Alon.

2. tcl_msql

3. Transpose in SQL

4. SQL SendMail

5. Transpose a SQL query

6. Perm Job in London

7. Transpose in SQL

8. Spell Checker for FPW26

9. matrix transpose in SQL?

10. How to transpose in Transact SQL

11. T-SQL - Need help with transposing data

12. Help SQL: How to TRANSPOSE a Table ?