February 21, 2007 at 2:21 pm
I am not sure if this is the right place (I could not find anyplace with sql 2000)to post a sql 2000 question, let me know if there is a place for me to post. Anyway, I have a sql 2000 table like this:
ID col1 col2
7777 itemx 12/02/07 00:00:10
7777 itemy 12/02/07 10:00:00
7777 itemz 12/02/07 12:10:60
8888 itemA 12/02/07 01:01:00
888 itemB 12/02/07 02:00:00
.......................................................
I like to combine all rows with the same ID together like the followings:
7777 itemx itemy itemz 12/02/07
888 itemA itemB.........................
The question has 3 parts:
1) what is the best way to do this? using trigger/stored procedure or just a vb sub with somesort of datarepeater, datalist controls
2) can you show me some examples of each way if any?
3) Can crystal report do something like this?
I am open to create another table or just plain writting them out on page.
Thanks
February 22, 2007 at 10:05 pm
This is something like cross-tab report technique. Do try script below:
DECLARE @Col1 Varchar(50)
DECLARE @SQL Varchar(5000)
DECLARE @Num INT
DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT Col1 FROM TestItem
GROUP BY Col1
ORDER BY Col1
SET @SQL = ''
SET @Num = 0
OPEN Fields
FETCH NEXT FROM Fields INTO @Col1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Num = @Num + 1
IF @Num = 1
SET @SQL = 'CASE WHEN COUNT(CASE WHEN ISNULL(CONVERT(varchar(100),Col1),''NA'') = ''' + @Col1 + ''' THEN [ID] ELSE NULL END)=0 THEN '''' ELSE ''' + @Col1 + ''' END
'
ELSE
SET @SQL = @SQL + '+ CASE WHEN COUNT(CASE WHEN ISNULL(CONVERT(varchar(100),Col1),''NA'') = ''' + @Col1 + ''' THEN [ID] ELSE NULL END)=0 THEN '''' ELSE ''' + @Col1 + ''' END
'
FETCH NEXT FROM Fields INTO @Col1
END
CLOSE Fields DEALLOCATE Fields
SET @SQL = 'SELECT ID, ' + @SQL + ' ,Col2
FROM TestItem
GROUP BY ID, Col2
ORDER BY ID, Col2
'
--PRINT @SQL
EXEC (@SQL)
February 22, 2007 at 10:10 pm
Sorry, you need this script for table and data population:
-- Create table [TestItem]
if exists (select * from dbo.sysobjects where id = object_id(N'[TestItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TestItem]
Create Table TestItem ([ID] INT NULL, Col1 Varchar(20) NULL, Col2 DateTime NULL)
-- Populate data
Insert Into TestItem
SELECT 7777,'itemx','12/Feb/2007' Union All
SELECT 7777, 'itemy', '12/Feb/2007' Union All
SELECT 7777, 'itemz', '12/Feb/2007' Union All
SELECT 8888, 'itemA', '12/Feb/2007' Union All
SELECT 888, 'itemB', '12/Feb/2007'
February 26, 2007 at 3:31 pm
Thanks Terry, I will give it a try.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply