SQL Server long column into multiple shorter columns

Question

I am working using SQL Server for a client and they have given me a request.

They have a table products with many columns, one of which is article. There are about 28,000 rows in products.

They wish to create a new table articles that contains only the article numbers from products, but set up so that the new table takes the article column and splits it into 3 columns each with (up to) 10,000 rows. I explained that this is not the best software to do this in but they insist (and they're the ones paying me!). The new table has columns Article1, Artcile2, and Article3.

Can someone help me out with this?

All I have succeeded in so far is getting the first 10,000 article numbers in correctly using

insert into articles (Article1) 
    select top 10000 article
    from products

But now I am stuck as to how to insert the remaining values into the 2nd and 3rd columns. I know what I really need is some sort of UPDATE query but I can't get anywhere.

I am running SSMS 2014.

Answer 1

If I understand your question correctly, you want to transform rows of article into a table with 3 columns. Here is a try:

;WITH Cte AS(
    SELECT article,
        grp = (ROW_NUMBER() OVER(ORDER BY article) -1) %
                (SELECT CEILING(COUNT(*) / (3 * 1.0)) FROM products)
    FROM products
),
CteFinal AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY grp ORDER BY article)
    FROM Cte
)
INSERT INTO articles(Article1, Article2, Article3)
SELECT
    Article1 = MAX(CASE WHEN rn = 1 THEN article END),
    Article2 = MAX(CASE WHEN rn = 2 THEN article END),
    Article3 = MAX(CASE WHEN rn = 3 THEN article END)
FROM CteFinal
GROUP BY grp

Answer 2

You can use NOT EXISTS clause, like when inserting in articles2 table, you can check it like NOT EXISTS (SELECT ARTICLE_ID FROM ARTICLES2). I hope you are getting my point.

Apart from that, if you can modify the structure of main ARTICLE table, then you can add a column with the name say PROCESSED (it would be a boolean column, with default value as 0). Once you have inserted data in articles1 table, you can update PROCESSED column for those articles to be 1, then you can process the remaining articles(which have processed column value = 0) for article2 and then update those with processed = 1, similarly for articles 3. Let me know if you have any questions.

Answer 3

Use the ID of the table products.

WITH NumberedMyTable AS
(
    SELECT
        Article,
        ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
    FROM
        Products
);

Insert INTO Articles1
SELECT
    Articles
FROM
    NumberedMyTable
WHERE
    RowNumber BETWEEN @From AND @To

Just change the @From and @To for the 3 tables.

1st table : @From = 1, @To = 10000

2nd table : @From = 10001, @To = 20000

3rd table : @From = 20001, @To = 28000

Answer 4

Wow... I love hearing about how others know how to design a database efficiently (not), such as you are being forced. Although you may have the given table of columns 1, 2, 3 etc, and you know it does not make sense, try creating the table in the more efficient format. Then, give them a VIEW that shows the articles 1, 2, 3 in a single row format that they want. As long as it can be efficient, might that help both your situation to implement, yet give them the output they need?

As for doing something like this, and they may want the most recent "x" number of articles vs first, you might want to add a column to the product table indicating the last ARTICLE SEQUENCE used for a given product. So, during the insert into the articles table, it gets the next sequence number by adding 1 from the products holding column. Then, when the insert is complete, it updates the product holding column for the next article.

This way, a product could have something like...

productid  lastArticleSeq
1          4
2          23
3          18
4          27
etc...

And your articles table could have ...

articleid   productid   articleseq
1           1           1
2           4           1
3           1           2
4           3           1
5           4           2
...etc.

This way, if they were interested in a query of that last 3 articles, you could use the lastArticleSeq and draw from that.