sravan.here (4/18/2013)
Hi,Yes please see the Create table script for source table and the insert script as well
CREATE TABLE yt
([ID] int, [Name] varchar(14), [text] varchar(21))
;
INSERT INTO yt
([ID], [Name], [text])
VALUES
(1, 'SID,DOB', '123,12/01/1990'),
(2, 'City,State,Zip', 'NewYork,NewYork,01234'),
(3, 'SID,DOB', '456,12/21/1990')
;
Now the out put should have 2 tables as i mentione in my question. and these values in the source table are getting populated dynamically so i cannot hardcode them in my script for generating the output tables.
This is an unusual transport file with metadata (partial, column names only) and data as string on the same row. Where / how was it generated? How does it deal with embedded quotes?
There are two row types in the sample, one with metadata defining two columns 'SID,DOB' and the other defining three columns 'City,State,Zip'. The key question is - are they constant throughout the table? Are there any rows having 'Street,City,State,Zip' as the metadata?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden