[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Need a query...

GB

3/27/2007 11:09:00 PM

Hello,
I have a table tblInput like this:
CREATE TABLE [dbo].[tblInput(
[NameID] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date] [datetime] NULL,
[Value] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

with data (sorry, no SQL INSERT...):

NameID |Date |Value
-------------------------------------
1 01/12/2001 12:00:00 AM A
1 01/12/2001 12:00:00 AM B
1 01/12/2001 12:00:00 AM C
1 01/12/2001 12:00:00 AM A
1 01/12/2001 12:00:00 AM C
1 02/12/2001 12:00:00 AM B
1 02/12/2001 12:00:00 AM B
1 02/12/2001 12:00:00 AM A
2 01/12/2001 12:00:00 AM C
2 01/12/2001 12:00:00 AM D
2 01/12/2001 12:00:00 AM C

I need a SELECT statement to get a recordset like this:

NameID |Date |A |B |C |D
--------------------------------------------------------------
1 01/12/2001 12:00:00 AM 0.4 0.2 0.4 0
1 02/12/2001 12:00:00 AM 0 0.66 0.33 0
2 01/12/2001 12:00:00 AM 0 0 0.66 0.33


Thanks,
GB
7 Answers

Steve Dassin

3/28/2007 12:43:00 AM

0


"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:eHRyBvNcHHA.4004@TK2MSFTNGP06.phx.gbl...
> This type of pivoting is normally best done in a reporting tool, ...

Every hear of Rac? :P :)

www.rac4sql.net

xyb

3/28/2007 1:35:00 AM

0

On 3?28?, ??7?08?, "GB" <v7v...@hotmail.com> wrote:
> Hello,
> I have a table tblInput like this:
> CREATE TABLE [dbo].[tblInput(
> [NameID] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Date] [datetime] NULL,
> [Value] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> with data (sorry, no SQL INSERT...):
>
> NameID |Date |Value
> -------------------------------------
> 1 01/12/2001 12:00:00 AM A
> 1 01/12/2001 12:00:00 AM B
> 1 01/12/2001 12:00:00 AM C
> 1 01/12/2001 12:00:00 AM A
> 1 01/12/2001 12:00:00 AM C
> 1 02/12/2001 12:00:00 AM B
> 1 02/12/2001 12:00:00 AM B
> 1 02/12/2001 12:00:00 AM A
> 2 01/12/2001 12:00:00 AM C
> 2 01/12/2001 12:00:00 AM D
> 2 01/12/2001 12:00:00 AM C
>
> I need a SELECT statement to get a recordset like this:
>
> NameID |Date |A |B |C |D
> --------------------------------------------------------------
> 1 01/12/2001 12:00:00 AM 0.4 0.2 0.4 0
> 1 02/12/2001 12:00:00 AM 0 0.66 0.33 0
> 2 01/12/2001 12:00:00 AM 0 0 0.66 0.33
>
> Thanks,
> GB

sql2000:(untested)
select NameID,Date,A=(Ac+0.0)/Count(*),B=(Bc+0.0)/Count(*),C=(Cc+0.0)/
Count(*),D=(Dc+0.0)/Count(*)
FROM
(
select NameID,Date,
Ac = sum(case when Value = 'a' then 1 else 0 end ),
Bc = sum(case when Value = 'b' then 1 else 0 end ),
Cc = sum(case when Value = 'c' then 1 else 0 end ),
Dc = sum(case when Value = 'd' then 1 else 0 end )
from tblInput
group by NameID,Date
) TBL
group by NameID,Date

Plamen Ratchev

3/28/2007 1:50:00 AM

0

This type of pivoting is normally best done in a reporting tool, but here it
is:

SELECT NameId,
[Date],
SUM(CASE Value WHEN 'A' THEN 1.0 ELSE 0 END) / SUM(1) AS A,
SUM(CASE Value WHEN 'B' THEN 1.0 ELSE 0 END) / SUM(1) AS B,
SUM(CASE Value WHEN 'C' THEN 1.0 ELSE 0 END) / SUM(1) AS C,
SUM(CASE Value WHEN 'D' THEN 1.0 ELSE 0 END) / SUM(1) AS D
FROM tblInput
WHERE Value IN ('A', 'B', 'C', 'D')
GROUP BY NameId, [Date]
ORDER BY NameId, [Date]

I added the WHERE clause to keep the percentage calculation correct if you
have more codes in the Value column (like F or G). If you have a check
constraint and allow only A, B, C, and D then you can drop it.

Also, if you are on SQL Server 2005 you an look up PIVOT.

HTH,

Plamen Ratchev
http://www.SQL...

Plamen Ratchev

3/28/2007 4:30:00 AM

0

Yes, it might be just the tool for that... :)

Plamen Ratchev
http://www.SQL...

bob

3/28/2007 11:36:00 AM

0

SQL 2005 only solution using new OVER clause

--DROP TABLE #tblInput
GO
CREATE TABLE #tblInput
(
[NameID] [nchar](10),
[Date] [datetime] NULL,
[Value] [nchar](10)
)
GO

SET NOCOUNT ON

INSERT INTO #tblInput VALUES ( 1, '01/12/2001 12:00:00 AM', 'A' )
INSERT INTO #tblInput VALUES ( 1, '01/12/2001 12:00:00 AM', 'B' )
INSERT INTO #tblInput VALUES ( 1, '01/12/2001 12:00:00 AM', 'C' )
INSERT INTO #tblInput VALUES ( 1, '01/12/2001 12:00:00 AM', 'A' )
INSERT INTO #tblInput VALUES ( 1, '01/12/2001 12:00:00 AM', 'C' )
INSERT INTO #tblInput VALUES ( 1, '02/12/2001 12:00:00 AM', 'B' )
INSERT INTO #tblInput VALUES ( 1, '02/12/2001 12:00:00 AM', 'B' )
INSERT INTO #tblInput VALUES ( 1, '02/12/2001 12:00:00 AM', 'A' )
INSERT INTO #tblInput VALUES ( 2, '01/12/2001 12:00:00 AM', 'C' )
INSERT INTO #tblInput VALUES ( 2, '01/12/2001 12:00:00 AM', 'D' )
INSERT INTO #tblInput VALUES ( 2, '01/12/2001 12:00:00 AM', 'C' )

SET NOCOUNT OFF
GO

-- Use the new OVER() clause
SELECT NameId, Date
, (A + .00) / total_records AS A
, (B + .00) / total_records AS B
, (C + .00) / total_records AS C
, (D + .00) / total_records AS D
FROM
(
SELECT *, COUNT(*) OVER( PARTITION BY NameId, Date ) AS total_records
FROM #tblInput
) x
PIVOT ( COUNT( Value ) For Value In ( [A], [B], [C], [D] ) ) pvt
ORDER BY Date, NameId


Let me know how you get on.

wBob

"GB" wrote:

> Hello,
> I have a table tblInput like this:
> CREATE TABLE [dbo].[tblInput(
> [NameID] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Date] [datetime] NULL,
> [Value] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> with data (sorry, no SQL INSERT...):
>
> NameID |Date |Value
> -------------------------------------
> 1 01/12/2001 12:00:00 AM A
> 1 01/12/2001 12:00:00 AM B
> 1 01/12/2001 12:00:00 AM C
> 1 01/12/2001 12:00:00 AM A
> 1 01/12/2001 12:00:00 AM C
> 1 02/12/2001 12:00:00 AM B
> 1 02/12/2001 12:00:00 AM B
> 1 02/12/2001 12:00:00 AM A
> 2 01/12/2001 12:00:00 AM C
> 2 01/12/2001 12:00:00 AM D
> 2 01/12/2001 12:00:00 AM C
>
> I need a SELECT statement to get a recordset like this:
>
> NameID |Date |A |B |C |D
> --------------------------------------------------------------
> 1 01/12/2001 12:00:00 AM 0.4 0.2 0.4 0
> 1 02/12/2001 12:00:00 AM 0 0.66 0.33 0
> 2 01/12/2001 12:00:00 AM 0 0 0.66 0.33
>
>
> Thanks,
> GB
>
>
>

GB

3/28/2007 8:39:00 PM

0

Thanks Plamen, it works for me perfectly.

GB


"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:eHRyBvNcHHA.4004@TK2MSFTNGP06.phx.gbl...
> This type of pivoting is normally best done in a reporting tool, but here
it
> is:
>
> SELECT NameId,
> [Date],
> SUM(CASE Value WHEN 'A' THEN 1.0 ELSE 0 END) / SUM(1) AS A,
> SUM(CASE Value WHEN 'B' THEN 1.0 ELSE 0 END) / SUM(1) AS B,
> SUM(CASE Value WHEN 'C' THEN 1.0 ELSE 0 END) / SUM(1) AS C,
> SUM(CASE Value WHEN 'D' THEN 1.0 ELSE 0 END) / SUM(1) AS D
> FROM tblInput
> WHERE Value IN ('A', 'B', 'C', 'D')
> GROUP BY NameId, [Date]
> ORDER BY NameId, [Date]
>
> I added the WHERE clause to keep the percentage calculation correct if you
> have more codes in the Value column (like F or G). If you have a check
> constraint and allow only A, B, C, and D then you can drop it.
>
> Also, if you are on SQL Server 2005 you an look up PIVOT.
>
> HTH,
>
> Plamen Ratchev
> http://www.SQL...
>
>
>

GB

3/28/2007 8:41:00 PM

0

Thanks Bob, it definitely works, but I still use 80 compatibility level...

GB


"Bob" <Bob@discussions.microsoft.com> wrote in message
news:108F935B-C455-40A5-A45B-BBC157F532F6@microsoft.com...
> SQL 2005 only solution using new OVER clause
>
> --DROP TABLE #tblInput
> GO
> CREATE TABLE #tblInput
> (
> [NameID] [nchar](10),
> [Date] [datetime] NULL,
> [Value] [nchar](10)
> )
> GO
>
> SET NOCOUNT ON
>
> INSERT INTO #tblInput VALUES ( 1, '01/12/2001 12:00:00 AM', 'A' )
> INSERT INTO #tblInput VALUES ( 1, '01/12/2001 12:00:00 AM', 'B' )
> INSERT INTO #tblInput VALUES ( 1, '01/12/2001 12:00:00 AM', 'C' )
> INSERT INTO #tblInput VALUES ( 1, '01/12/2001 12:00:00 AM', 'A' )
> INSERT INTO #tblInput VALUES ( 1, '01/12/2001 12:00:00 AM', 'C' )
> INSERT INTO #tblInput VALUES ( 1, '02/12/2001 12:00:00 AM', 'B' )
> INSERT INTO #tblInput VALUES ( 1, '02/12/2001 12:00:00 AM', 'B' )
> INSERT INTO #tblInput VALUES ( 1, '02/12/2001 12:00:00 AM', 'A' )
> INSERT INTO #tblInput VALUES ( 2, '01/12/2001 12:00:00 AM', 'C' )
> INSERT INTO #tblInput VALUES ( 2, '01/12/2001 12:00:00 AM', 'D' )
> INSERT INTO #tblInput VALUES ( 2, '01/12/2001 12:00:00 AM', 'C' )
>
> SET NOCOUNT OFF
> GO
>
> -- Use the new OVER() clause
> SELECT NameId, Date
> , (A + .00) / total_records AS A
> , (B + .00) / total_records AS B
> , (C + .00) / total_records AS C
> , (D + .00) / total_records AS D
> FROM
> (
> SELECT *, COUNT(*) OVER( PARTITION BY NameId, Date ) AS total_records
> FROM #tblInput
> ) x
> PIVOT ( COUNT( Value ) For Value In ( [A], [B], [C], [D] ) ) pvt
> ORDER BY Date, NameId
>
>
> Let me know how you get on.
>
> wBob
>
> "GB" wrote:
>
> > Hello,
> > I have a table tblInput like this:
> > CREATE TABLE [dbo].[tblInput(
> > [NameID] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [Date] [datetime] NULL,
> > [Value] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> >
> > with data (sorry, no SQL INSERT...):
> >
> > NameID |Date |Value
> > -------------------------------------
> > 1 01/12/2001 12:00:00 AM A
> > 1 01/12/2001 12:00:00 AM B
> > 1 01/12/2001 12:00:00 AM C
> > 1 01/12/2001 12:00:00 AM A
> > 1 01/12/2001 12:00:00 AM C
> > 1 02/12/2001 12:00:00 AM B
> > 1 02/12/2001 12:00:00 AM B
> > 1 02/12/2001 12:00:00 AM A
> > 2 01/12/2001 12:00:00 AM C
> > 2 01/12/2001 12:00:00 AM D
> > 2 01/12/2001 12:00:00 AM C
> >
> > I need a SELECT statement to get a recordset like this:
> >
> > NameID |Date |A |B |C |D
> > --------------------------------------------------------------
> > 1 01/12/2001 12:00:00 AM 0.4 0.2 0.4 0
> > 1 02/12/2001 12:00:00 AM 0 0.66 0.33 0
> > 2 01/12/2001 12:00:00 AM 0 0 0.66 0.33
> >
> >
> > Thanks,
> > GB
> >
> >
> >