April 27, 2010 at 3:36 am
Here's your query, Jason.
Hope you find it helpful.
--CREATE TABLE
IF OBJECT_ID('Tempdb..#meterReading') IS NOT NULL DROP TABLE #meterReading
CREATE TABLE #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
INSERT INTO #meterReading VALUES(1646893,111078,'PREP','2008-05-23 16:30:00.000',1,3,5)
INSERT INTO #meterReading VALUES(1646893,111078,'PREP','2008-05-23 16:30:00.000',2,1,5)
INSERT INTO #meterReading VALUES(1648693,111078,'DN','2008-06-02 00:00:00.000',1,3,5)
INSERT INTO #meterReading VALUES(1648693,111078,'DN','2008-06-02 00:00:00.000',2,9,5)
INSERT INTO #meterReading VALUES(1658044,111078,'INIT','2008-06-02 00:00:00.000',1,3,6)
INSERT INTO #meterReading VALUES(1658044,111078,'INIT','2008-06-02 00:00:00.000',2,9,6)
INSERT INTO #meterReading VALUES(1648703,111078,'I','2008-06-02 09:20:00.000',1,3,5)
INSERT INTO #meterReading VALUES(1648703,111078,'I','2008-06-02 09:20:00.000',2,9,5)
INSERT INTO #meterReading VALUES(1648717,111078,'LI','2008-06-02 09:25:00.000',1,3,5)
INSERT INTO #meterReading VALUES(1648717,111078,'LI','2008-06-02 09:25:00.000',2,9,5)
INSERT INTO #meterReading VALUES(1651816,111078,'WF','2008-06-19 10:30:00.000',1,336,5)
INSERT INTO #meterReading VALUES(1651816,111078,'WF','2008-06-19 10:30:00.000',2,4820,5)
INSERT INTO #meterReading VALUES(1656084,111078,'K','2008-06-24 11:30:00.000',1,494,5)
INSERT INTO #meterReading VALUES(1656084,111078,'K','2008-06-24 11:30:00.000',2,6147,5)
INSERT INTO #meterReading VALUES(1657665,111078,'EMAI','2008-06-30 00:00:00.000',1,725,5)
INSERT INTO #meterReading VALUES(1657665,111078,'EMAI','2008-06-30 00:00:00.000',2,8769,5)
INSERT INTO #meterReading VALUES(1660526,111078,'R','2008-07-18 14:45:00.000',1,1094,5)
INSERT INTO #meterReading VALUES(1660526,111078,'R','2008-07-18 14:45:00.000',2,13737,5)
INSERT INTO #meterReading VALUES(1661015,111078,'K','2008-07-23 12:45:00.000',1,1173,5)
INSERT INTO #meterReading VALUES(1661015,111078,'K','2008-07-23 12:45:00.000',2,14715,5)
INSERT INTO #meterReading VALUES(1665633,111078,'DN','2008-08-26 00:00:00.000',1,1173,5)
INSERT INTO #meterReading VALUES(1665633,111078,'DN','2008-08-26 00:00:00.000',2,14715,5)
INSERT INTO #meterReading VALUES(1665663,111078,'DN','2008-08-27 00:00:00.000',1,1173,5)
INSERT INTO #meterReading VALUES(1665663,111078,'DN','2008-08-27 00:00:00.000',2,14715,5)
INSERT INTO #meterReading VALUES(1667979,111078,'EMAI','2008-08-31 00:00:00.000',1,2002,6)
INSERT INTO #meterReading VALUES(1667979,111078,'EMAI','2008-08-31 00:00:00.000',2,23955,6)
INSERT INTO #meterReading VALUES(1675123,111078,'EMAI','2008-09-30 00:00:00.000',1,2334,5)
INSERT INTO #meterReading VALUES(1675123,111078,'EMAI','2008-09-30 00:00:00.000',2,30568,5)
INSERT INTO #meterReading VALUES(1681873,111078,'EMAI','2008-10-31 00:00:00.000',1,3259,5)
INSERT INTO #meterReading VALUES(1681873,111078,'EMAI','2008-10-31 00:00:00.000',2,39199,5)
INSERT INTO #meterReading VALUES(1688666,111078,'EMAI','2008-11-30 00:00:00.000',1,7933,6)
INSERT INTO #meterReading VALUES(1688666,111078,'EMAI','2008-11-30 00:00:00.000',2,45843,6)
-- PIVOT QUERY
SELECT mReadingCallReference, mReadingDeviceReference, mReadingReadingType, mReadingMeterReadingDate,
[1],[2]
FROM #meterReading AS MR
PIVOT(AVG(mReadingMeterReading) FOR mReadingMeterType IN ([1],[2])) AS pvt
-- Gianluca Sartori
April 27, 2010 at 3:39 am
Jason, you could have closely followed either mine or Gianluca's code and with further modifications, you could have accomplished the task..
But anyways, here is your code
First, the table structure (teh sample data you provided was not easily consumable, pls take care of tat in future)
IF OBJECT_ID('TEMPDB..#meterReading') IS NOT NULL
DROP TABLE #meterReading
CREATE TABLE #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]
INSERT INTO #meterReading
SELECT 1646893,111078,'PREP','2008-05-23 16:30:00.000',1,3,5
UNION ALL
SELECT
1646893,111078,'PREP','2008-05-23 16:30:00.000',2,1,5
UNION ALL
SELECT
1648693,111078,'DN','2008-06-02 00:00:00.000',1,3,5
UNION ALL
SELECT
1648693,111078,'DN','2008-06-02 00:00:00.000',2,9,5
UNION ALL
SELECT
1658044,111078,'INIT','2008-06-02 00:00:00.000',1,3,6
UNION ALL
SELECT
1658044,111078,'INIT','2008-06-02 00:00:00.000',2,9,6
UNION ALL
SELECT
1648703,111078,'I','2008-06-02 09:20:00.000',1,3,5
UNION ALL
SELECT
1648703,111078,'I','2008-06-02 09:20:00.000',2,9,5
UNION ALL
SELECT
1648717,111078,'LI','2008-06-02 09:25:00.000',1,3,5
UNION ALL
SELECT
1648717,111078,'LI','2008-06-02 09:25:00.000',2,9,5
UNION ALL
SELECT
1651816,111078,'WF','2008-06-19 10:30:00.000',1,336,5
UNION ALL
SELECT
1651816,111078,'WF','2008-06-19 10:30:00.000',2,4820,5
UNION ALL
SELECT
1656084,111078,'K','2008-06-24 11:30:00.000',1,494,5
UNION ALL
SELECT
1656084,111078,'K','2008-06-24 11:30:00.000',2,6147,5
UNION ALL
SELECT
1657665,111078,'EMAI','2008-06-30 00:00:00.000',1,725,5
UNION ALL
SELECT
1657665,111078,'EMAI','2008-06-30 00:00:00.000',2,8769,5
UNION ALL
SELECT
1660526,111078,'R','2008-07-18 14:45:00.000',1,1094,5
UNION ALL
SELECT
1660526,111078,'R','2008-07-18 14:45:00.000',2,13737,5
UNION ALL
SELECT
1661015,111078,'K','2008-07-23 12:45:00.000',1,1173,5
UNION ALL
SELECT
1661015,111078,'K','2008-07-23 12:45:00.000',2,14715,5
UNION ALL
SELECT
1665633,111078,'DN','2008-08-26 00:00:00.000',1,1173,5
UNION ALL
SELECT
1665633,111078,'DN','2008-08-26 00:00:00.000',2,14715,5
UNION ALL
SELECT
1665663,111078,'DN','2008-08-27 00:00:00.000',1,1173,5
UNION ALL
SELECT
1665663,111078,'DN','2008-08-27 00:00:00.000',2,14715,5
UNION ALL
SELECT
1667979,111078,'EMAI','2008-08-31 00:00:00.000',1,2002,6
UNION ALL
SELECT
1667979,111078,'EMAI','2008-08-31 00:00:00.000',2,23955,6
UNION ALL
SELECT
1675123,111078,'EMAI','2008-09-30 00:00:00.000',1,2334,5
UNION ALL
SELECT
1675123,111078,'EMAI','2008-09-30 00:00:00.000',2,30568,5
UNION ALL
SELECT
1681873,111078,'EMAI','2008-10-31 00:00:00.000',1,3259,5
UNION ALL
SELECT
1681873,111078,'EMAI','2008-10-31 00:00:00.000',2,39199,5
UNION ALL
SELECT
1688666,111078,'EMAI','2008-11-30 00:00:00.000',1,7933,6
UNION ALL
SELECT
1688666,111078,'EMAI','2008-11-30 00:00:00.000',2,45843,6
And the PIVOT Query
SELECT [mReadingCallReference] ,
[mReadingDeviceReference] ,
[mReadingReadingType] ,
[mReadingMeterReadingDate] ,
[1] [mReadingMeterType1] ,
[2] [mReadingMeterType2]
FROM
(SELECT * FROM #meterReading ) PIVOT_TABLE
PIVOT
(MAX(mReadingMeterReading) for [mReadingMeterType] IN ([1],[2])) PIVOT_HANDLE
hope this helped you...
Cheers!!
April 27, 2010 at 3:44 am
Gianluca Sartori (4/27/2010)
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!
Oh Oh, Google failed me for the first time 😛
And anyways, thanks Gianluca. Am more than learning SQL here 🙂
Now, Grazie -:)
April 27, 2010 at 3:49 am
ColdCoffee (4/27/2010)
Gianluca Sartori (4/27/2010)
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!
Oh Oh, Google failed me for the first time 😛
And anyways, thanks Gianluca. Am more than learning SQL here 🙂
Now, Grazie -:)
Prego 🙂
-- Gianluca Sartori
April 27, 2010 at 4:37 am
Hi Gianluca and ColdCoffee
Thanks for the help guys. Currently working through Gianluca's dynamic generating example and converting it to my table.
I have a question though about the first part of the statement used to produce the list of possible values.
Why do we have the "For XML path ''" and the 1,1, space[0] ?
ColdCoffee what do I need to do in future with Table Schemas ?
Thanks Again for all the help, the PIVOT command looks extremely useful and will be used in many bits of code.
April 27, 2010 at 4:48 am
jasonshaw (4/27/2010)Why do we have the "For XML path ''" and the 1,1, space[0] ?
It's a "trick" to generate on the fly a string with concatenation of all the values returned by the query. It uses FOR XML PATH('') to generate an XML where the root element is an empty string. The STUFF(@sql, 1,1,space(0)) simply replaces the first comma with an empty string.
ColdCoffee what do I need to do in future with Table Schemas ?
I think he means that you could have provided your sample data in the form of "INSERT INTO #SampleData VALUES(...)", which is more readily consumable for those who try to help you.
Thanks Again for all the help, the PIVOT command looks extremely useful and will be used in many bits of code.
You're welcome, glad I could help.
-- Gianluca Sartori
April 27, 2010 at 4:52 am
Why do we have the "For XML path ''" and the 1,1, space[0] ?
FOR XML PATH concatenates the rows values present in the column which you want to become as rows, with a delimiter.
STUFF(STRING, 1, 1 , SPACE(0) ) removes the delimiter string that pops up due to the above concatenation.
ColdCoffee what do I need to do in future with Table Schemas ?
Jason, please go thro the following link and you will understand a clear Table Schemas help us , and inturn , you , to get the best code possible..
April 27, 2010 at 4:59 am
Gianluca Sartori (4/27/2010)
jasonshaw (4/27/2010)Why do we have the "For XML path ''" and the 1,1, space[0] ?
It's a "trick" to generate on the fly a string with concatenation of all the values returned by the query. It uses FOR XML PATH('') to generate an XML where the root element is an empty string. The STUFF(@sql, 1,1,space(0)) simply replaces the first comma with an empty string.
Oh man, you beat me third time in the same thread. Feels good that i can also code like you..it really is an honor to be on par with u (i can only be par on T-SQL coding, other areas you reply to are waay above my head), Gianluca..
ColdCoffee what do I need to do in future with Table Schemas ?
I think he means that you could have provided your sample data in the form of "INSERT INTO #SampleData VALUES(...)", which is more readily consumable for those who try to help you.
Spot On. A question with the following things provided in the required format will certainly help everyone who views a thread!
1. Table Structures - CREATE TABLE scripts
2. Constraints or Indexes in the table, - CREATE SCRIPTS
3. SAMPLE DATA - INSERT INTO TABLE scripts
4. Desired output - some visual representation of this.
Cheers!!
April 27, 2010 at 5:36 am
ColdCoffee (4/27/2010)
Oh man, you beat me third time in the same thread. Feels good that i can also code like you..it really is an honor to be on par with u (i can only be par on T-SQL coding, other areas you reply to are waay above my head), Gianluca..
You must be joking, right? There's lots of people it would be an honor to be on par with, but not me, really.
Anyway it's not a challenge, nobody's "beating" anybody, it's just about helping people and, hopefully, learn something new.
See you around the forums, my friend!
-- Gianluca Sartori
April 30, 2010 at 8:01 am
Hi Everybody
Manage to get a stored procedure to work using a parameter to find values for a particular
mReadingDeviceReference.
ALTER PROCEDURE [dbo].[usp_meter_combine]
-- Add the parameters for the stored procedure here
@unq_ref int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @sql nvarchar(4000)
SET @sql =
STUFF
(
(
SELECT DISTINCT
',' + QUOTENAME(mReadingMeterType) AS [text()]
FROM dbo.meterReading
where mReadingDeviceReference = @unq_ref
FOR XML PATH('')
)
, 1, 1, SPACE(0));
print '-' + @sql + '-'
declare @str_unq nvarchar(10)
set @str_unq = cast(@unq_ref as nvarchar)
print '-' + @str_unq + '-'
SET @sql = '
;WITH Test AS (
SELECT mReadingCallReference
,mReadingDeviceReference
,mReadingReadingType
,mReadingMeterReadingDate
,mReadingMeterType
,mReadingMeterReading
FROM dbo.meterReading
)
SELECT mReadingCallReference
,mReadingDeviceReference
,mReadingReadingType
,mReadingMeterReadingDate, ' + @sql +
' FROM dbo.meterReading AS T
PIVOT (AVG(mReadingMeterReading) FOR mReadingMeterType IN (' + @sql +
')) AS pvt where mReadingDeviceReference = ' + @str_unq +
' order by mReadingMeterReadingDate desc'
print @sql
EXEC sp_executesql @sql
END
This seemed to work for most machines I used the stored procedure for, but when the last row in the table was different on one mReadingCallReference it would produce two rows of data.
mReadingCallReference
mReadingDeviceReference
mReadingReadingType
mReadingMeterReadingDate12
1800716107784PO2010-03-30 00:00:00.00021615111899
1799719107784PAGE2010-03-10 00:00:00.00021615111899
1795077107784R2010-03-09 15:30:00.00021615111886
1792231107784R2010-03-05 09:40:00.00021552NULL
1792231107784R2010-03-05 09:40:00.000NULL111132
1792089107784FAX2010-02-28 00:00:00.00021462NULL
1792089107784FAX2010-02-28 00:00:00.000NULL109719
1789049107784PO2010-02-23 00:00:00.00021235NULL
1789049107784PO2010-02-23 00:00:00.000NULL106083
(Sorry about messy table - not sure how to format nicely on this site yet)
I can understand there is something about grouping by the other columns in the table but I have not selected the column that causes this.
Found a post this morning by Jeff Moden http://www.sqlservercentral.com/articles/Crosstab/65048/ Which describes Pivots & Cross Tabs as a follow up to http://www.sqlservercentral.com/articles/T-SQL/63681/
The use of cross tabs seem to produce what I was looking for. So will try and change the stored procedure to this method.
Jason
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply