loop through temporary table in SQL

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Jigsaw:

Do you want the concatenated list ordered? If so, how?

|||

Jigsaw:

Here is a way to do this using a CTE:

declare @.mockup table
(ID INT NOT NULL ,PK INT , pocket VARCHAR(10))

insert into @.mockup
select 1, 1, 'p1' union all
select 2, 1, 'p2' union all
select 3, 2, 'p3' union all
select 4, 2, 'p4' union all
select 5, 3, 'p5' union all
select 6, 3, 'p6' union all
select 7, 4, 'p7' union all
select 8, 5, 'p1' union all
select 9, 5, 'p2' union all
select 10,5, 'p83'
--select * from @.mockup

declare @.output table (PK int, pocketList varchar(200))

;with pocketList as
( select row_number () over
( partition by PK
order by pocket
) as seq,
row_number () over
( partition by PK
order by pocket desc
) as revSeq,
PK,
ID,
pocket
from @.mockup
), recursivePockets as
( select seq,
revSeq,
PK,
pocket,
cast(pocket as varchar(max)) as [product]
from pocketList
where revSeq = 1
union all
select b.seq,
b.revSeq,
b.PK,
b.pocket,
cast(b.pocket + ', ' + product as varchar(max))
from recursivePockets a
inner join pocketList b
on b.revSeq = a.revSeq + 1
and b.pk = a.pk
)

insert into @.output
select pk,
product
from recursivePockets
where seq = 1
order by pk

-- pk product
-- -- -
-- 1 p1, p2
-- 2 p3, p4
-- 3 p5, p6
-- 4 p7
-- 5 p1, p2, p83

( Ooops! Almost forgout to load it into an output table; sorry )

|||Have a read of FOR XML

Example

SELECT ' '+rtrim(ltrim(pocket))
from mytable
where pk = 2
order by pocket
FOR XML PATH('')

This will return you 1 row with a space in between each item you want to concat

Lookup including looking up on null values possible?

In order to insert datekey values in I lookup datekey in the datedimension table. I join on the 'Date' column that contains dates. The datedimension contains one record for 'unknown date' for which the value of the 'Date' column is null.

The behavior that I desire from my lookup transformation is that for input records with a date the corresponding datekey from the datedimension is looked up and for records with date = null, the datekey for 'unknown date' is looked up.

The first part works well but the looking up on null fails, apparently because you can not say null == null. Does anyone know whether there is a setting in the lookup transformation to include null lookups?

Thnx,
HenkThe lookup transform can not do this. You would need to put a derived column in the flow and if the value is NULL then set it to the appropriate 'unknown date' value.

Thanks,|||Thanks Matt.|||In fact it can and it is quite easy! I found out in the documentation:

"A Lookup transformation that has been configured to use partial or no caching will fail if a lookup operation matches columns that contain null values, unless you manually update the SQL statement to include an OR ISNULL(ColumnName) condition. If full precaching is used, the lookup operation succeeds."

|||So by selecting the full precaching option for the lookup, you eliminate the need to modify the SQL with the ISNULL function?|||While this can work as described I would recommend against it and is, therefore, why I didn't mention it. You need to be careful if you do lookups in this way because unless you guarrantee that there is only one such value you will get the first one lookup happens to find with no warning.

Full precaching will not work because the cache is fully charged and doesn't issue the SQL statement again. The reason why partial or no cache works is because the SQL statement is issued if a match isn't found and will return success due to the ISNULL statement as long as there is a NULL in the table.

There are too many ifs and caveats to make this a good solution, IMHO.

Thanks,

Looking for the equivalence (date issue)

Hi all of you,

1)Ok, this works properly:

ISNULL([Column 13]) ? NULL(DT_WSTR,1) : RIGHT([Column 13],2) + "/" + SUBSTRING([Column 13],5,2) + "/" + SUBSTRING([Column 13],1,4)

for (old dts2000 column transformation)

Fecha=right(DTSSource("Col014"),2) & "-" & Mid(DTSSource("Col014"),5,2) & "-" & left(DTSSource("Col014"),4)
If IsNull(Fecha) then
DTSDestination("FechaAp") = Fecha
Else
DTSDestination("FechaAp")=null
End If

2). But how must I do such thing for IsDate when you have not available that function?

?

Fecha=right(DTSSource("Col014"),2) & "-" & Mid(DTSSource("Col014"),5,2) & "-" & left(DTSSource("Col014"),4)
If IsDate(Fecha) then
DTSDestination("FechaAp") = Fecha
Else
DTSDestination("FechaAp")=null
End If

Thanks a lot,

I think you'll need to use a script component to do this check. You can use the DateTime.TryParse method.

|||Thanks for that. It'll be tested.

Looking for query help

Can anyone see why this only works if an address had no enddate? Something is going wrong in the WHERE Clause, if the enddate is null it works fine. What I need is to look at the enddate and if it is Null or >= today the record should be returned. I only want to use the Month and Day of the date.

Thank you for any help,

CREATE PROCEDURE [dbo].[sp_DataReaderName] @.SID int AS
SELECT
 CASE
 WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN C.[FirstName] +" "+ C.[LastName]
 ELSE A.[CompanyName]
 END AS DRName,
 C.Client_ID
FROM
 tblClients C
INNER JOIN
 tblClientAddresses A ON C.Client_ID = A.Client_ID
WHERE
 (C.Client_ID = @.SID) AND
 (A.MailTo=1) AND
 (A.EndDate Is Null OR (DatePart(m,A.Enddate) >= DatePart(m,GETDATE()) AND DatePart(d,A.Enddate) >= DatePart(d,GETDATE())))
GO

Its late and I've not spotted anything obvious except...DON'T prefix with "sp_" you'll incur a needless performance cost. SQL Server will think its a Microsoft Proc and go straight to Master.