Databases
StackOverflow2013
Postsdbo
POHow to split CSVs from one column to rows in a new table in MSSQL 2008 R2
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to split CSVs from one column to rows in a new table in MSSQL 2008 R2
    primarykey
    data
    text
    <p>Imagine the following (very bad) table design in MSSQL2008R2:</p> <pre><code>Table "Posts": | Id (PK, int) | DatasourceId (PK, int) | QuotedPostIds (nvarchar(255)) | [...] | 1 | 1 | | [...] | 2 | 1 | 1 | [...] | 2 | 2 | 1 | [...] [...] | 102322 | 2 | 123;45345;4356;76757 | [...] </code></pre> <p>So, the column QuotedPostIds contains a semicolon-separated list of self-referencing PostIds (Kids, don't do that at home!). Since this design is ugly as a hell, I'd like to extract the values from the QuotedPostIds table to a new n:m relationship table like this:</p> <pre><code>Desired new table "QuotedPosts": | QuotingPostId (int) | QuotedPostId (int) | DatasourceId (int) | | 2 | 1 | 1 | | 2 | 1 | 2 | [...] | 102322 | 123 | 2 | | 102322 | 45345 | 2 | | 102322 | 4356 | 2 | | 102322 | 76757 | 2 | </code></pre> <p>The primary key for this table could either be a combination of QuotingPostId, QuotedPostId and DatasourceID or an additional artificial key generated by the database.</p> <p>It is worth noticing that the current Posts table contains about 6,300,000 rows but only about 285,000 of those have a value set in the QuotedPostIds column. Therefore, it might be a good idea to pre-filter those rows. In any case, I'd like to perform the normalization using internal MSSQL functionality only, if possible. </p> <p>I already read other posts regarding this topic which mostly dealt with split functions but neither could I find out how exactly to create the new table and also copying the appropriate value from the Datasource column, nor how to filter the rows to touch accordingly. </p> <p>Thank you!</p> <p><strong>€dit:</strong> I thought it through and finally solved the problem using an external C# program instead of internal MSSQL functionality. Since it seems that it could have been done using Mikael Eriksson's suggestion, I will mark his post as an answer.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USShackles
    1. USShackles
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to split CSVs from one column to rows in a new table in MSSQL 2008 R2
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you show some code what you have done / where you have problems? Whats your problem with split? You won't get a full featured solution coded here.
      singulars
      1. POHow to split CSVs from one column to rows in a new table in MSSQL 2008 R2
      1. USJan
    2. COI basically have a table funtion that can extract the semicolon-separated values from a given data row. What I don't know is how to apply that to every row in my table and how to add the result to a new table.
      singulars
      1. POHow to split CSVs from one column to rows in a new table in MSSQL 2008 R2
      1. USShackles
    3. COBecause only constants and @local_variables can be passed to table-valued functions, you have to declare a cursor and step to all of your rows when you want to use a table function.
      singulars
      1. POHow to split CSVs from one column to rows in a new table in MSSQL 2008 R2
      1. USJan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload