fixed column output- looping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

fixed column output- looping

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-03-20 : 16:44:08

I have table like below

ID AMT
1001 1234.560
1001 34.560
1001 134.000
1002 45.000
1002 3456.000
1003 5678.999

I need to create a fixed length data file..For example, ID char(6) and amt num(10.3) and sum(23.3)

It should be group and order by ID : 01 Represent ID line and 02 represent amt ( there can be multiple amt records) and 03 represent sum of amt.
01 + ID
02 + AMT
03 + Sum(AMT)

The output should look like this.. How do this either using a t-sql or SSIS?

011001
021234.560
0234.560
02134.00
031403.12
011002
0245.000
023456.000
033501.000
011003
025678.999
035678.999

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-21 : 02:45:35

I give up.
I could just come upto this.

declare @tbl table(id int,amt numeric(10,3),newnum numeric(10,3))
insert into @tbl
select 1001 ,1234.560,0 union all
select 1001 ,34.560,0 union all
select 1001 ,134.000,0 union all
select 1002 ,45.000,0 union all
select 1002 ,3456.000,0 union all
select 1003 ,5678.999,0

declare @i numeric(10,3)

;with cte(id,amt,rowid,newnum)as
(
select * from ( select id,amt,row_number()over(partition by id order by id)as rowid,newnum from @tbl
)t
)
update cte set @i=newnum=case when rowid>=1 then '2'+ '' + cast(amt as varchar(10))

end

select id,stuff(newnum,1,1,'02')as newnum from @tbl
union all
select id,stuff(sum(newnum),1,1,'03')as newnum from @tbl group by id

Output is--
Id NewNum
1001 021234.560
1001 0234.560
1001 02134.000
1002 0245.000
1002 023456.000
1003 025678.999
1001 033603.120
1002 033701.000
1003 035678.999

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-21 : 03:25:40

Hi,

Try with this

declare @Temp table(id int,amt numeric(10,3))
insert into @Temp
select 1001 ,1234.560 union all
select 1001 ,34.560 union all
select 1001 ,134.000 union all
select 1002 ,45.000 union all
select 1002 ,3456.000 union all
select 1003 ,5678.999

Declare @T Table (Col1 Varchar(100), Amt DEcimal(18,3), Id int)
Insert into @T
Select SubString(CAST (id as varchar(100)),3,3), AMT, Id From @Temp

Declare @Table Table (id int Identity(1,1),Col1 INT, AMT DEcimal(18,3))
Insert into @Table (Col1, AMT)
Select col1,Sum(Amt) from @T group bY Col1

Select distinct '01' + Cast(Id As Varchar(100)) From @T
union all
select '02' + CAST(Amt as varchar(1000)) From @T
union all
Select '03' + CAST(AMT AS Varchar(1000)) From @Table

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-03-21 : 10:22:54

is there anyway i can do this using a cursor ? Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-23 : 14:03:34

Try this:-


SELECT '01'+CAST(ID AS Varchar(100))+ CHAR(13) + t2.list + '03'+ CAST(t3.Total as varchar(1000))
FROM Table
CROSS APPLY(SELECT '02'+ AMT + CHAR(13) AS [text()]
            FROM Table
            WHERE ID=t1.ID
            FOR XML PATH(''))t2(list)
CROSS APPLY (SELECT SUM(AMT) AS Total
             FROM Table
             WHERE ID=t1.ID)t3

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-23 : 20:58:58

Heh... you try it... several conversion errors and output that looks nothing like requested.

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-23 : 21:36:27

Ok...

1. You don't need a cursor for this.
2. You don't need the RBAR of either a CROSS APPLY or a correlated subquery.
3. Like anything else, "Divide and Conquer" prevails.

Here's the test code...

--==========================================================
-- Create and populate a test table.
-- THIS IS NOT PART OF THE SOLUTION.
--==========================================================
    SET NOCOUNT ON
 CREATE TABLE #yourtable
        (
        ID  INT,
        Amt NUMERIC(10,3)
        )

 INSERT INTO #yourtable
        (ID,Amt)
 SELECT 1001, 1234.560 UNION ALL
 SELECT 1001, 34.560   UNION ALL
 SELECT 1001, 134.000  UNION ALL
 SELECT 1002, 45.000   UNION ALL
 SELECT 1002, 3456.000 UNION ALL
 SELECT 1003, 5678.999 

--==========================================================
-- This is one possible solution.  The advantage here is
-- that there are no correlated sub-queries or CROSS APPLY
-- which means that each section can be tested or modified
-- separately.  It's the ol' "Divide and Conquer" method.
--==========================================================
;WITH cteRows AS
(--==== Record Type "01" - Header info
 SELECT STR(ID)+'01' AS SortOrder,
        '01' + CAST(ID AS VARCHAR(10)) AS TheText
   FROM #yourtable
  GROUP BY ID
  UNION ALL ------------------------------------------
 --==== Record Type "02" - Detail info
 SELECT STR(ID)+'02' AS SortOrder,
        '02' + CAST(Amt AS VARCHAR(20)) AS TheText
   FROM #yourtable
  UNION ALL ------------------------------------------
 --==== Record Type "03" - Sum of Detail info
 SELECT STR(ID)+'03' AS SortOrder,
        '03'+ CAST(SUM(Amt) AS VARCHAR(20)) AS TheText
   FROM #yourtable
  GROUP BY ID
)
 SELECT TheText
   FROM cteRows
  ORDER BY SortOrder

--===== Cleanup after demo (NOT PART OF THE SOLUTION)
   DROP TABLE #yourtable

Lemme know if that does it for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources