Hello everyone, this is my first post here so hopefully I am not asking a common question.
I am trying to create a flat dataset in SQL 2005. Basically I run a query and I get multiple rows for the same primary key. The query I am running is quite large and has several different tables connected to it, here is a small sample of what it looks like...
Typeid(Primary Key) Individual Address
1 Sam 912 Ave. J
1 Sam 913 Ave. Q
1 Sam 914 Ave. R
2 Mike 1000 Ave. O
3 Jill 1001 Ave. O
I want it to kind of look like this
TypeID Individual Address_1 Address_2 Address_3
1 Sam 912 Ave. J 913 Ave. Q 914 Ave. R
2 Mike 1000 Ave. O
3 Jill 1001 Ave. O
As I said before, this query is pretty big, and has several variables like Address where multiple rows are being taken by one Primary Key.
If it is not possible to do this in SQL 2005, is there a program that may be able to? Right now we are using SPSS as sort of a bandaid... we run the query in small portions like the one in the sample and then restructure the in sections but this takes several hours.
Anyways, thanks for any help that you may be able to provide.
-John
It seems like you 'should' be able to create a VIEW that has the flattened data (results of a multi-table JOIN).
Then you can export to your DH using bcp, SQLCmd, or SSIS.
|||
You could use a Common Table Expression to generate a result set that would be more like
ID Name Addresses
1 Sam 912 Ave J., 913 Ave J., 914 Ave J.
2 Tom 1 A St
3 Henry 4 Fifth St
Would that be desirable?
|||That would be great, but I am not sure how to go about doing that. Could you explain or provide an example?
Thanks
-John
|||Have you considered using the PIVOT "relational operator" in SQL 2K5? If you have a predictable maximum number of addresses, it would be easier, but you might be able to do something dynamic.|||Try this one out, though you might have to change it a bit for your particular situation.
DECLARE @.ID int
DECLARE @.address nvarchar(50)
DECLARE @.ALLaddress nvarchar(MAX)
IF EXISTS ( SELECT * FROM sys.tables where [name] = 'Addresses_Flat')
DROP TABLE Addresses_Flat
CREATE TABLE Addresses_Flat
(ID int, [Name] nvarchar(50), [Address] nvarchar(MAX))
DECLARE IDCursor CURSOR FOR
SELECT DISTINCT id
FROM MyTest
OPEN IDCursor
WHILE 1=1 BEGIN
FETCH next FROM IDCursor INTO @.ID
IF @.@.FETCH_STATUS <> 0 BREAK
DECLARE AddressCursor CURSOR FOR
SELECT [Address]
FROM MyTest
WHERE ID = @.ID
SET @.Alladdress = ''
OPEN AddressCursor
WHILE 1=1 BEGIN
FETCH next FROM AddressCursor INTO @.address
IF @.@.FETCH_STATUS <> 0 BREAK
SET @.ALLaddress = @.ALLaddress + ', ' + @.address
END
CLOSE AddressCursor
DEALLOCATE AddressCursor
INSERT INTO Addresses_Flat
SELECT DISTINCT id, [name], @.ALLaddress as [Address]
FROM MyTest
WHERE ID = @.ID
END
CLOSE IDCursor
DEALLOCATE IDCursor
SELECT * FROM Addresses_Flat
DROP TABLE Addresses_Flat
