June 5, 2009 at 8:38 am
I have a table structured as following :
ATTR LIB
1row01
2row02
3row03
1row11
2row12
3row13
1row21
2row22
3row23
and I want to transform the structure to build a view as :
COL1 COL2 COL3
row01 row02 row03
row11 row12 row13
row21 row22 row 23
I do not seen how to write the script to perform the transformation. Thanks for all helps.
June 5, 2009 at 9:20 am
Not really sure how this is going to help you, but based on what you posted, here is an answer as an attachment. Again, I seem to be having issues with code that I try to cut and paste.
June 5, 2009 at 9:49 am
Hi
Just another possible solution
DECLARE @t TABLE (Attr INT, Lib VARCHAR(10))
INSERT INTO @t
SELECT 1, 'row01'
UNION ALL SELECT 2, 'row02'
UNION ALL SELECT 3, 'row03'
UNION ALL SELECT 1, 'row11'
UNION ALL SELECT 2, 'row12'
UNION ALL SELECT 3, 'row13'
UNION ALL SELECT 1, 'row21'
UNION ALL SELECT 2, 'row22'
UNION ALL SELECT 3, 'row23'
; WITH
cte (Attr, Lib, RowNum) AS
(
SELECT
Attr,
Lib,
ROW_NUMBER() OVER (PARTITION BY Attr ORDER BY (SELECT 1))
FROM @t
)
SELECT
a1.Lib,
a2.Lib,
a3.Lib
FROM cte a1
JOIN cte a2 ON a1.RowNum = a2.RowNum
JOIN cte a3 ON a1.RowNum = a3.RowNum
WHERE
a1.Attr = 1
AND a2.Attr = 2
AND a3.Attr = 3
But I think Lynn's approach should perform better 😀
How to Post Data/Code to get the best Help How to Post Performance Problems
June 5, 2009 at 10:50 am
Thanks for yours quicky replies, but I have made a mistake in my question.
I would obtain eventually:
COL1 COL2 COL3
row01 row11 row21
row02 row12 row22
row03 row13 row23
June 5, 2009 at 11:09 am
Here is my suggestion at this point, take the code you have been provided, and see if you can make the necessary changes to meet your new requirements. If so, post the code you came up with to solve the problem. If not, post the code you came up but isn't solving the problem, and show us what it is doing so we may be able to help you fix it (or come up with something total different).
June 5, 2009 at 2:55 pm
Agreed, I've been building an example based off of Lynn's code for the past 15 minutes, and have already come up with like 9 other ways of doing this, similar to the partition by approach (minus CTE) above.
I'm perplexed how this would even be useful. Without more context the best approach is the text-based "GROUP BY LEFT(Lib, 4)" cross-tab approach, which is extremely hard-coded but the only answer that makes sense based off of the limited amount of "purpose" in the question.
June 5, 2009 at 3:27 pm
Thanks for all yours helps.
Finally, I use the Florian Reischl's script as base. For my database, the Lynn Pettis's one is not adapted because of the group by instruction : row01 was only an example and my datas are not too structured.
CREATE TABLE [dbo].[TEST](
[ID] [int] NULL,
[NUM_COLONNE] [int] NULL,
[LIBELLE] [varchar](50) NULL
) ON [PRIMARY]
WITH CT_TRANSPOSE(ID, NUM_COLONNE, LIBELLE, ROWNUM) AS
(
SELECT
ID,
NUM_COLONNE,
LIBELLE,
ROW_NUMBER() OVER (PARTITION BY NUM_COLONNE ORDER BY (ID))
FROM TEST
)
SELECT
A1.ID,
A1.LIBELLE AS COL_01,
A2.LIBELLE AS COL_02,
A3.LIBELLE AS COL_03,
A4.LIBELLE AS COL_04,
A5.LIBELLE AS COL_05,
A6.LIBELLE AS COL_06
FROM
CT_TRANSPOSE A1
JOIN CT_TRANSPOSE A2 ON A1.ROWNUM = A2.ROWNUM
JOIN CT_TRANSPOSE A3 ON A1.ROWNUM = A3.ROWNUM
JOIN CT_TRANSPOSE A4 ON A1.ROWNUM = A4.ROWNUM
JOIN CT_TRANSPOSE A5 ON A1.ROWNUM = A5.ROWNUM
JOIN CT_TRANSPOSE A6 ON A1.ROWNUM = A6.ROWNUM
WHERE
A1.NUM_COLONNE = 1
AND A2.NUM_COLONNE = 2
AND A3.NUM_COLONNE = 3
AND A4.NUM_COLONNE = 4
AND A5.NUM_COLONNE = 5
AND A6.NUM_COLONNE = 6
ID COL_01 COL_02 COL_03 COL_04 COL_05 COL_06
1row01row02row03row04row05row06
7row11row12row13row14row15row16
13row21row22row23row24row25row26
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply