April 27, 2010 at 12:25 am
Hi to everybody, first excuse me for my english, i'm italian.
I need some help to build a "complex" query for me.
i have this table
CREATE TABLE [Test] (
[datax] [smalldatetime] NOT NULL ,
[StatoImp] [bit] NOT NULL ,
[Imp] [smallint] NOT NULL ,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[datax],
[Imp]
) ON [PRIMARY]
) ON [PRIMARY]
GO
with this values
INSERT dbo.Test VALUES ('2010-04-15 00:00:00.000', 1, 1)
INSERT dbo.Test VALUES ('2010-04-15 00:00:00.000', 0, 2)
INSERT dbo.Test VALUES ('2010-04-15 00:00:00.000', 1, 3)
INSERT dbo.Test VALUES ('2010-04-16 00:00:00.000', 0, 1)
INSERT dbo.Test VALUES ('2010-04-16 00:00:00.000', 0, 2)
INSERT dbo.Test VALUES ('2010-04-16 00:00:00.000', 1, 3)
INSERT dbo.Test VALUES ('2010-04-17 00:00:00.000', 0, 1)
INSERT dbo.Test VALUES ('2010-04-17 00:00:00.000', 0, 2)
INSERT dbo.Test VALUES ('2010-04-17 00:00:00.000', 1, 3)
INSERT dbo.Test VALUES ('2010-04-18 00:00:00.000', 1, 1)
INSERT dbo.Test VALUES ('2010-04-18 00:00:00.000', 0, 2)
INSERT dbo.Test VALUES ('2010-04-18 00:00:00.000', 0, 3)
i want to have a recordset like this
datax , statoimp1, statoimp2, statoimp3, statoimpX ... from ....
where 'statoimp1' is statoimp of imp = 1 in datax = 2010-04-15
where 'statoimp2' is statoimp of imp = 2 in datax = 2010-04-15
etc.
second ROW
where 'statoimp1' is statoimp of imp = 1 in datax = 2010-04-16
where 'statoimp2' is statoimp of imp = 2 in datax = 2010-04-16
Can anyone help me ?
thank's a lot 🙂
bye
April 27, 2010 at 1:56 am
Hi Alessandro,
it's nice to find other Italians on this site.
can you please clarify what values do you expect in every column? What would the results look like with the sample data you provided?
Ciao Alessandro,
è un piacere trovare altri Italiani in questo sito!
Potresti spiegare quali valori vorresti estrarre in ogni colonna? Come sarebbe il set di risultati con i dati di esempio che hai fornito?
-- Gianluca Sartori
April 27, 2010 at 2:07 am
this is the recordset that i need.
Of course in my example the is 3 Imp, but in real i have n Imp and i must have n column in my recordset
DATAX | StatoImp1 | StatoImp2 | StatoImp3
2010-04-15 | 1| 0| 1
2010-04-16 | 0| 0| 1
2010-04-17 | 0| 0| 1
2010-04-18 | 1| 0| 0
Grazie è un piacere anche per mè 😉
Thank's
Grazie a tutti
April 27, 2010 at 2:17 am
This should do the trick for you:
--CREATE TEST TEMPORARY TABLE
IF OBJECT_ID('Tempdb..#Test') IS NOT NULL DROP TABLE #Test
CREATE TABLE #Test (
[datax] [smalldatetime] NOT NULL ,
[StatoImp] [bit] NOT NULL ,
[Imp] [smallint] NOT NULL ,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[datax],
[Imp]
)
)
--INSERT SAMPLE DATA
INSERT #Test VALUES ('2010-04-15 00:00:00.000', 1, 1)
INSERT #Test VALUES ('2010-04-15 00:00:00.000', 0, 2)
INSERT #Test VALUES ('2010-04-15 00:00:00.000', 1, 3)
INSERT #Test VALUES ('2010-04-16 00:00:00.000', 0, 1)
INSERT #Test VALUES ('2010-04-16 00:00:00.000', 0, 2)
INSERT #Test VALUES ('2010-04-16 00:00:00.000', 1, 3)
INSERT #Test VALUES ('2010-04-17 00:00:00.000', 0, 1)
INSERT #Test VALUES ('2010-04-17 00:00:00.000', 0, 2)
INSERT #Test VALUES ('2010-04-17 00:00:00.000', 1, 3)
INSERT #Test VALUES ('2010-04-18 00:00:00.000', 1, 1)
INSERT #Test VALUES ('2010-04-18 00:00:00.000', 0, 2)
INSERT #Test VALUES ('2010-04-18 00:00:00.000', 0, 3)
;WITH Test AS (
SELECT datax,
CAST(StatoImp AS tinyint) AS StatoImp,
Imp
FROM #Test
)
SELECT datax, [1] as statoimp1, [2] as statoimp2, [3] as statoimp3
FROM Test AS T
PIVOT (AVG(StatoImp) FOR Imp IN ([1],[2],[3])) AS pvt
Please note that I had to convert StatoImp to tinyint, because bit columns cannot be aggregated.
Unfortunately there's no way to pivot for any value automatically, you have to code the pivot for each expected value.
You could do it in dynamic sql, using EXEC or sp_executesql.
Hope this helps
Gianluca
-- Gianluca Sartori
April 27, 2010 at 2:18 am
Hi Everybody
Just come across your site today as I was looking at exactly the same problem.
I have a similar table with at the moment 2 different resulting columns but this will change in the future and could have more than 10 different values. I was trying to get a single row for each date to use a datagrid in C#. Been new to SQL server from Informix 4GL background I would be grateful for any insights.
Thank you
Jason Shaw
April 27, 2010 at 2:22 am
Jason, post your table scripts, some sample data and the desired output and I'll be glad to help.
-- Gianluca Sartori
April 27, 2010 at 2:22 am
hey buddy, i did not understand fully your requirement, but i have a pivot query which might interest you..
SELECT datax , [1] StatoImp1, [2] StatoImp3, [3] StatoImp3 FROM
(SELECT datax, CAST(statoimp AS INT) statoimp, imp FROM dbo.Test ) PIVOT_TABLE
PIVOT
( MAX(statoimp) FOR Imp IN ([1],[2],[3])) PIVOT_HANDLE
Hope this helps you..
Cheeers!
April 27, 2010 at 2:25 am
Gianluca, you beat me to it 😎
And Alessandro, as he said convert the bit column into tinyint (i used INT though) and then you can aggregate..
Cheers!!
April 27, 2010 at 2:28 am
ColdCoffee (4/27/2010)
hey buddy, i did not understand fully your requirement, but i have a pivot query which might interest you..
SELECT datax , [1] StatoImp1, [2] StatoImp3, [3] StatoImp3 FROM
(SELECT datax, CAST(statoimp AS INT) statoimp, imp FROM dbo.Test ) PIVOT_TABLE
PIVOT
( MAX(statoimp) FOR Imp IN ([1],[2],[3])) PIVOT_HANDLE
Hope this helps you..
Cheeers!
It's exactly the same thing. ColdCoffe used a MAX aggregate and I used a AVG aggregate, but this depends on you business logic. If it's guaranteed to have unique values for each [imp]/[datax] value, then you can use any aggregate you like and it won't change the results.
-- Gianluca Sartori
April 27, 2010 at 2:52 am
mm well done, thank's to everybody.
i hope to find a clear query can dinamically make a pivot technique.
But i think this is very very hard to do, or impossible maybe.
The only way is use Dynamic Sql Exec ... or (better for me) compose sql string in C# o Vb.net previous connect to Database
Thank's to everybody and happy programming 🙂
April 27, 2010 at 3:01 am
It's not difficult to build the query dynamically:
DECLARE @sql nvarchar(4000)
SET @sql =
STUFF
(
(
SELECT DISTINCT
',' + QUOTENAME(imp) AS [text()]
FROM #test
FOR XML PATH('')
)
, 1, 1, SPACE(0));
SET @sql = '
;WITH Test AS (
SELECT datax,
CAST(StatoImp AS tinyint) AS StatoImp,
Imp
FROM #Test
)
SELECT datax, ' + @sql + '
FROM Test AS T
PIVOT (AVG(StatoImp) FOR Imp IN (' + @sql + ')) AS pvt
'
EXEC sp_executesql @sql
Hope this is what you're after.
-- Gianluca Sartori
April 27, 2010 at 3:07 am
Alessandro, Sei il benvenuto!
April 27, 2010 at 3:19 am
ColdCoffee (4/27/2010)
Alessandro, Sei il benvenuto!
Aaah! You fell victim of the "google translator" bug!
I think you wanted to translate "You're welcome", that's "Prego" in Italian.
"Sei il benvenuto" means literally "you are welcome", in the meaning of somebody's presence in some place.
Nice attempt, anyway!
-- Gianluca Sartori
April 27, 2010 at 3:19 am
ColdCoffee (4/27/2010)
Alessandro, Sei il benvenuto!
Grazie ! 🙂
@Gianluca Sartori, wow ! great work, i know FOR XML PATH('') is "magic" for situation like this.
OK is very clear now, i don't like very much dynamic Sql for performance and "philosophy", but in
Italy say "desperate situation, desperate measures".
bye
April 27, 2010 at 3:24 am
Hi Everyone
Table Schema
CREATE TABLE [dbo].[meterReading](
[mReadingCallReference] [int] NULL,
[mReadingDeviceReference] [int] NULL,
[mReadingReadingType] [varchar](4) NULL,
[mReadingMeterReadingDate] [datetime] NULL,
[mReadingMeterType] [tinyint] NULL,
[mReadingMeterReading] [int] NULL,
[mReadingStatus] [tinyint] NULL
) ON [PRIMARY]
Sample Data
1646893,111078,PREP,2008-05-23 16:30:00.000,1,3,5
1646893,111078,PREP,2008-05-23 16:30:00.000,2,1,5
1648693,111078,DN,2008-06-02 00:00:00.000,1,3,5
1648693,111078,DN,2008-06-02 00:00:00.000,2,9,5
1658044,111078,INIT,2008-06-02 00:00:00.000,1,3,6
1658044,111078,INIT,2008-06-02 00:00:00.000,2,9,6
1648703,111078,I,2008-06-02 09:20:00.000,1,3,5
1648703,111078,I,2008-06-02 09:20:00.000,2,9,5
1648717,111078,LI,2008-06-02 09:25:00.000,1,3,5
1648717,111078,LI,2008-06-02 09:25:00.000,2,9,5
1651816,111078,WF,2008-06-19 10:30:00.000,1,336,5
1651816,111078,WF,2008-06-19 10:30:00.000,2,4820,5
1656084,111078,K,2008-06-24 11:30:00.000,1,494,5
1656084,111078,K,2008-06-24 11:30:00.000,2,6147,5
1657665,111078,EMAI,2008-06-30 00:00:00.000,1,725,5
1657665,111078,EMAI,2008-06-30 00:00:00.000,2,8769,5
1660526,111078,R,2008-07-18 14:45:00.000,1,1094,5
1660526,111078,R,2008-07-18 14:45:00.000,2,13737,5
1661015,111078,K,2008-07-23 12:45:00.000,1,1173,5
1661015,111078,K,2008-07-23 12:45:00.000,2,14715,5
1665633,111078,DN,2008-08-26 00:00:00.000,1,1173,5
1665633,111078,DN,2008-08-26 00:00:00.000,2,14715,5
1665663,111078,DN,2008-08-27 00:00:00.000,1,1173,5
1665663,111078,DN,2008-08-27 00:00:00.000,2,14715,5
1667979,111078,EMAI,2008-08-31 00:00:00.000,1,2002,6
1667979,111078,EMAI,2008-08-31 00:00:00.000,2,23955,6
1675123,111078,EMAI,2008-09-30 00:00:00.000,1,2334,5
1675123,111078,EMAI,2008-09-30 00:00:00.000,2,30568,5
1681873,111078,EMAI,2008-10-31 00:00:00.000,1,3259,5
1681873,111078,EMAI,2008-10-31 00:00:00.000,2,39199,5
1688666,111078,EMAI,2008-11-30 00:00:00.000,1,7933,6
1688666,111078,EMAI,2008-11-30 00:00:00.000,2,45843,6
What I am looking to produce is
1646893,111078,PREP,2008-05-23 16:30:00.000,3,1
1648693,111078,DN,2008-06-02 00:00:00.000,3,9
1658044,111078,INIT,2008-06-02 00:00:00.000,3,9
1648703,111078,I,2008-06-02 09:20:00.000,3,9
1648717,111078,LI,2008-06-02 09:25:00.000,3,9
1651816,111078,WF,2008-06-19 10:30:00.000,336,4820
1656084,111078,K,2008-06-24 11:30:00.000,494,6147
etc...
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply