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