from rows to columns 4

[mimi2]

Hello,
I have a table like this:
id date
11 2002/05/01
11 2002/07/01
22 2002/06/01
22 2002/07/01
22 2002/09/01
i would like to produce a report (a table...)
with this info:
id date date date difference
11 2002/05/01 2002/07/01 diff1
22 2002/06/01 2002/07/01 2002/09/01 diff2 diff3

diff1= nombre of days betrween 2002/05/01 and 2002/07/01
diff2= nombre of days betrween 2002/06/01 and 2002/07/01
diff3= nombre of days betrween 2002/07/01 and 2002/09/01

i think i need to create a temporary table, do you have any idea ?
thanks

 

[Transcend]

*ahem*

Why do you have duplicate values in your id column ... or are the two columns together the primary key ...

Anyways I'm working on the temp table idea .. my heads just a little fuzzy

Transcend

 

[MeanGreen]

I am not sure if you really want diff1 or the difference in days, so I actually calculated the difference in days for each row. I think this will work for you:

create table t1 (id integer,thedate datetime)

insert t1 values (11,'05/1/2002')
insert t1 values (11,'07/1/2002')
insert t1 values (22,'06/1/2002')
insert t1 values (22,'07/1/2002')
insert t1 values (22,'09/1/2002')

SELECT DISTINCT T1.ID
,CASE WHEN T2.CNT = 2 THEN CONVERT(VARCHAR(10),D1,101) + ' ' + CONVERT(VARCHAR(10),D2,101)
ELSE
CONVERT(VARCHAR(10),D1,101) + ' ' + (SELECT CONVERT(VARCHAR(10),THEDATE,101) FROM T1 WHERE THEDATE BETWEEN DATEADD(MINUTE,1,D1) AND DATEADD(MINUTE,-1,D2) AND T1.ID = T2.ID)
+ ' ' + CONVERT(VARCHAR(10),D2,101) END

,CASE WHEN T2.CNT = 2 THEN CONVERT(VARCHAR(4),DATEDIFF(D,D1,D2))
ELSE
CONVERT(VARCHAR(4),DATEDIFF(D,D1,(SELECT CONVERT(VARCHAR(10),THEDATE,101) FROM T1 WHERE THEDATE BETWEEN DATEADD(MINUTE,1,D1) AND DATEADD(MINUTE,-1,D2) AND T1.ID = T2.ID))) +
' ' + CONVERT(VARCHAR(4),DATEDIFF(D, (SELECT CONVERT(VARCHAR(10),THEDATE,101) FROM T1 WHERE THEDATE BETWEEN DATEADD(MINUTE,1,D1) AND DATEADD(MINUTE,-1,D2) AND T1.ID = T2.ID),D2)) END

FROM T1 JOIN(
SELECT ID,MIN(THEDATE)D1 ,MAX(THEDATE) D2,COUNT(*) CNT FROM T1
GROUP BY ID) T2
ON T1.ID = T2.ID
ORDER BY T1.ID

Hope this helps.

 

[mimi2]

Thanks for the answers.
I am puzzled by MeanGrean's answer !!
what is CASE WHEN T2.CNT = 2 ?
the number of dates changes from one client to another.
I understand that if i create a temporary table, it has to accomodate the biggest number of dates.

 

[MeanGreen]

The t2.Cnt was the count of the number of dates for each id. From what you had written, I thought the most you would have is 3, but it sounds like you might have more. With that in mind, you will want to adjust the query for the other sceanarios. It can be done, but it sounds like you want to use temp tables and cursors. I stay away from cursors as they can slow your processes down greatly. As for temp tables, I generally try to find a solution using SQL before I go to using temp tables. Since you may have more then 3 dates, I created this loop to generate your output without temp tables using my previous sample table t1.

declare @d1 datetime, @d2 datetime, @id integer, @maxid integer
select @id = min(id),@maxid = max(id) from t1
while @id <= @maxid
begin
select @d1 = min(TheDate),@d2 = max(TheDate) from t1 where id = @id
declare @sql varchar(2000)
declare @diff varchar(2000)
select @sql = ''
select @diff =''
while @d1 <= @d2
begin
select @sql = @sql + convert(varchar(10),@d1,101) + ' '
select @diff = @diff + isnull(convert(varchar(4),datediff(d,@d1,(select min(TheDate) from t1 where id = @id and TheDate > @d1))),'') + ' '
select @d1 = min(TheDate) from t1 where id = @id and TheDate > @d1
end
select @id id,rtrim(@sql) TheDate,rtrim(@diff) Diff
select @id = min(id) from t1 where id > @id
end

Hope this helps.

 

[cbeggan]

Did this before seeing MeanGreens reply. Does the same trick in the same way, but uses a temp table to format the output a bit and enables it to be selected in one go (or bcp'd). Pretty much complete bar the report header, which requires another loop from 1 to @maxlength to concatenate the header string.

-- Create a temporary  Output Table to output all report lines in one fell swoop
Create table #tempreport
( Record varchar(511) not null)


-- Declare Variables
DECLARE @Counter         int
DECLARE @Mindate         datetime
DECLARE @PreviousMinDate datetime
DECLARE @Maxdate         datetime
DECLARE @ReportLine1     varchar(255)
DECLARE @ReportLine2     varchar(255)
DECLARE @ReportLineTotal varchar(511)
DECLARE @CurrentID       int
DECLARE @MaxID           int
DECLARE @MaxLength       int

-- Initialise variables
SET @CurrentID   = 0
SET @Counter     = 0
SET @ReportLine1 = ''
SET @ReportLine2 = ''

-- Find the maximum number of dates you have - for formatting
SET @maxlength  =  (SELECT  TOP 1 Count(*)  from t1 group by id ORDER BY 1 DESC)
-- Need a loop to create the report header - have omitted this

SELECT @CurrentID =  min(id), @MaxID = max(id) from t1 where id > @CurrentID 

-- Begin Main loop
WHILE @CurrentID <=  @MaxID
BEGIN
   -- Add all dates for this ID, in date order
   SELECT @mindate = min(thedate), @maxdate = max(thedate) from  t1 where  id = @CurrentID    
   WHILE @mindate <= @maxdate
   BEGIN
      SET @counter = @counter + 1
      SET @Reportline1 = @ReportLine1  + ' '+ convert(varchar, @mindate,101)
      SET @reportline2 = @ReportLine2 + ' '+ ISNULL(convert(varchar, datediff(D,  @PreviousMinDate, @mindate)),'')
      SET @previousmindate = @mindate
      SELECT @MinDate = min(thedate) from t1 where  id = @CurrentID and thedate > @MinDate
   END --INNER WHILE LOOP

   -- Format shorter records to align properly 
   WHILE @Counter < @maxlength
   BEGIN
       SET @ReportLine1 = @ReportLine1 + '           '   -- Add 11 SPACES for formatting
       SET @Counter = @Counter + 1
   END

   -- Put the record pieces together
   SET @ReportLineTotal = rtrim(convert(char, @CurrentID)) + ' ' + @ReportLine1 + ' ' + @ReportLine2 
   
   --  Add to temp table for storage until finished
   INSERT into #TempReport VALUES ( @ReportLineTotal)

   -- Reinitialise variables for next ID
   SET @ReportLine1 = ''
   SET @ReportLine2 = ''
   SET @Counter = 0   
   SET @PreviousMinDate = NULL
   SELECT @CurrentID =  min(id) from t1 where id > @CurrentID 
END  --Outer WHILE LOOP

-- Dump Output and drop table
select * from #tempreport
drop table #tempreport[\code] ------
Dublin, Ireland.

 

[roody91]

Hello,

I hope I don't offend by barging in on this thread but I have a very similar problem to resolve and am very new to SQL programming.

My data is such:
alarm_id date_timestamp resp1 object
va170711 1/25/2003 12:09:44 AM PC b56fwjr
va170711 1/25/2003 12:10:26 AM RS b56fwjr
va170711 1/25/2003 1:07:37 AM RI bak9myz
xz170712 1/25/2003 12:09:56 AM PC b56fwjr
xz170712 1/25/2003 12:10:17 AM RS b56fwjr
va170712 1/25/2003 12:45:53 AM RP bak9myz
va170712 1/25/2003 12:46:19 AM TT b56fwjr
va170713 1/25/2003 12:35:49 AM RS b56fwjr
va170713 1/25/2003 1:07:55 AM TT bak9myz

I would like to put this data into columns like this:
alarm_id r_a_dt r_a_typ r_a_obj r_b_dt r_b_typ r_b_obj
va170711 1/25/03 PC b56fwjr 1/25/03 RS b56fwjr
xz170712 1/25/03 PC b56fwjr 1/25/03 RS b56fwjr
va170713 1/25/03 RS b56fwjr 1/25/03 TT bak9myz

The major difference i see from what I need to do and what mimi2 requested is that my &quot;id&quot; column is var not int.

I want to keep the dt fields as full datetime (I cut them for simplicity) and I need to take the &quot;rows to colums&quot; out to a level of 5 eg. r_e_dt r__e_typ r_e_obj(I can't fit an example on this page) . There may be more than 5 rows with the same alarm_id, I don't want to look at anything past row 5.

Any help would be appreciated.

FB

 

[Transcend]

roody91 you would be better off starting your own thread so that everyone will see your question.

Transcend

 

[cbeggan]

Looking at my previous reponse, you need to add a maximum of 5 to the inner while control (using the counter variable). Then set the Reportline variable to add the 3 required fields, you dont need the ReportLine2 stuff.

Your outer loop can still drive on alarm_id, the inner one on date_timestamp. The fact that your alarm_id is a var shouldn't matter. Your report will be ordered by alarm_id anyway as you are starting from the min value and working upwards. If your alarm_id's are mixed case (e.g. xa123123 XA123124) the lowever and upper cases will be ordered separately. Use CurrentID = min(lower(alarmid)) to remove this case-sensitivity...

You wont need any of the maxlength stuff either as that was just for tabulating the 'diffs' in the original problem...

Hope this helps... ------
Dublin, Ireland.

 

[mimi2]

Thanks a lot again. I'll try to follow (and learn !)the scripts that have been proposed. You really helped.

 

[Durkin]

Hi. I just had a quick look through this thread so forgive me if I'm way off the mark. You seem to be trying to create a pivot or crosstab query. Here is a script that will help you generate them on the fly or at least give you ideas:

http://www.databasejournal.com/scripts/article.php/1498681

Durkin

 

[roody91]

cbeggan

I've tried the your script and it does work but .....

I'm trying to convert the row data into individual columns for each resp1, userid and date time value with the alarm_id being the &quot;key&quot;. Your script is great but it throws all the data into a single column. Any ideas on what changes are needed to write into multiple columns?

Thanks for all your help!