Wednesday, March 28, 2012

Problems with sql-statement / making rows to columns...

I have the following problem:

I'd like to display these rows as columns via an sql-statement:

Input

Code Snippet

4 10000000 Juice
4 10000000 Coca-Cola
4 10000000 Orange
4 10000000 Walmart

Output

Code Snippet

4 10000000 Juice Coca-Cola Orange Walmart

To get the data within the input table, I momentarily use this select:

Code Snippet

SELECT artikel.lfdnr,

artikel.artnr1,

arteinordnung.einordnung

FROM artikel

LEFT JOIN aeinord ON artikel.lfdnr = aeinord.artnr

LEFT JOIN arteinordnung ON aeinord.kriterium = arteinordnung.kriterium

AND aeinord.einordnung = arteinordnung.lfdnr

ORDER BY artikel.lfdnr

Any suggestions on how to modify this statement to get the data into the output-format?

Any help's appreciated!

here you go..

Code Snippet

Create Table #data (

[Col1] int ,

[Col2] int ,

[Col3] varchar(20)

);

Insert Into #data Values('4','10000000','Juice');

Insert Into #data Values('4','10000000','Coca-Cola');

Insert Into #data Values('4','10000000','Orange');

Insert Into #data Values('4','10000000','Walmart');

Insert Into #data Values('5','10000000','Juice');

Insert Into #data Values('5','10000000','Coca-Cola');

Insert Into #data Values('5','10000000','Orange');

--For SQL Server 2005

;With CTE

as

(

Select Col1,Col2,Col3,Row_Number() Over (Partition By Col1,Col2 Order By Col1,Col2) Rowid From #Data

)

Select * From CTE

Pivot

(

Max(Col3) For Rowid in ([1],[2],[3],[4],[5]) --,...[n])

) as Pvt

/*

--If your intention is concatinate all the row data in one column

;With CTE

as

(

Select Col1,Col2,Col3,Row_Number() Over (Partition By Col1,Col2 Order By Col1,Col2) Rowid From #Data

)

, Result

as

(

Select * From CTE

Pivot

(

Max(Col3) For Rowid in ([1],[2],[3],[4],[5])--,...[n])

) as Pvt

)

Select

Col1

, Col2

, Isnull([1] +';','') + Isnull([2] +';','') + Isnull([3] +';','') + Isnull([4] +';','') + Isnull([5] +';','') as OneColumn

From

Result

*/

--For SQL Server 2000

Select * into #Temp From #Data

Alter table #Temp Add RowId Int Identity(1,1)

Alter table #Temp Add GroupedRowId int

Update #Temp

Set GroupedRowId = RowId - (Select Min(RowId)-1 From #Temp Sub Where Sub.Col1 = #Temp.Col1 And Sub.Col2 = #Temp.Col2)

Select

Col1,

Col2,

Max(Case When GroupedRowId=1 Then Col3 End),

Max(Case When GroupedRowId=2 Then Col3 End),

Max(Case When GroupedRowId=3 Then Col3 End),

Max(Case When GroupedRowId=4 Then Col3 End),

Max(Case When GroupedRowId=5 Then Col3 End)

-- ... ,Max(Case When GroupedRowId=n Then Col3 End)

From

#Temp

Group By

Col1,Col2

/*

--If your intention is concatinate all the row data in one column

Select

Col1,

Col2,

Isnull([1] +';','') + Isnull([2] +';','') + Isnull([3] +';','') + Isnull([4] +';','') + Isnull([5] +';','') as OneColumn

From

(

Select

Col1,

Col2,

Max(Case When GroupedRowId=1 Then Col3 End) [1],

Max(Case When GroupedRowId=2 Then Col3 End) [2],

Max(Case When GroupedRowId=3 Then Col3 End) [3],

Max(Case When GroupedRowId=4 Then Col3 End) [4],

Max(Case When GroupedRowId=5 Then Col3 End) [5]

-- ... ,Max(Case When GroupedRowId=n Then Col3 End)

From

#Temp

Group By

Col1,Col2

) as Result

*/

Drop Table #Temp

|||

Manivannan.D.Sekaran wrote:

here you go..

Thanks, that was a huge post

I fully understand your solution, but I have the problem that Juice, Coca-Cola,Orange and Walmart aren't the only possible values. There can be 1...n values within those row...

So, I need a solution that can extract dynamically those values without doing this manually:

Insert Into #data Values('4','10000000','Juice');

Insert Into #data Values('4','10000000','Coca-Cola');

Insert Into #data Values('4','10000000','Orange');

Insert Into #data Values('4','10000000','Walmart');

Insert Into #data Values('5','10000000','Juice');

Insert Into #data Values('5','10000000','Coca-Cola');

Insert Into #data Values('5','10000000','Orange');


Any suggestions?!
|||

It appears that Mani created a sample table, and loaded it with sample data so he could try to work out a solution for you. (Since you didn't bother to offer DDL or sample data.)

It would appear that perhaps you didn't really 'fully understand' Manivannan's suggested solution -since you didn't follow the creation and poputation of the sample data...

I think that the solution that follows the loading of the sample data could most likely be altered to fit your table structure -whatever that may be...

(It does look like he went the 'extra step'...)

|||

your select statement is write

for output:

you can format it inside the program

like that

duppose you put the artikel.lfdnr in x1 and artikel.artnr1 in x2 and arteinordnung.einordnung in x3

s= "4 10000000 "

if (x1=4 and x2= 10000000)

S=S+x3

|||

Ok, so instead of the creation process via "CREATE table #data" I'd let run my standard SQL SELECT. Then I've got all the possible values within the output table. Then I encounter this problem:

Code Snippet

;With CTE

as

(

Select Col1,Col2,Col3,Row_Number() Over (Partition By Col1,Col2 Order By Col1,Col2) Rowid From #Data

)

Select * From CTE

Pivot

(

Max(Col3) For Rowid in ([1],[2],[3],[4],[5]) --,...[n])

) as Pvt

It looks like I need to know all the possible values for this part, or am I wrong:

Code Snippet

Max(Col3) For Rowid in ([1],[2],[3],[4],[5]) --,...[n])

|||

Code Snippet

create function dbo.MultiList ( @.col1 as int, @.col2 as int )

returns varchar(8000)

as

begin

declare @.list varchar(8000)

select @.list = coalesce( @.list + ', ', '') + Col3

from data where Col1 = @.col1 and Col2 = @.col2

return @.list

end

GO

Create Table data (

[Col1] int ,

[Col2] int ,

[Col3] varchar(20)

);

Insert Into data Values('4','10000000','Juice');

Insert Into data Values('4','10000000','Coca-Cola');

Insert Into data Values('4','10000000','Orange');

Insert Into data Values('4','10000000','Walmart');

Insert Into data Values('5','10000000','Juice');

Insert Into data Values('5','10000000','Coca-Cola');

Insert Into data Values('5','10000000','Orange');

select col1, col2, dbo.MultiList(col1, col2) as items

from data

group by col1, col2

|||

Mh, maybe you're right. I am really struggling in setting up the correct SQL-statement...

At the moment I don't see the possibility to adapt the pivot-function to my SQL-statement.

|||

Rather than the CTE, you may be better served by examining the 'MultiList' function that DaleJ offered.

|||

The multilist function is completely new to me. How do I approach it at best?

To understand the functionality better I'd like to execute my standard SELECT, then I got all the relevant data within a table. Is it possible to skip this procedure in your multilist function, or do I need to set it up like that?

Code Snippet

Create Table data (

[Col1] int ,

[Col2] int ,

[Col3] varchar(20)

);

Somehow I feel not comfortable with the "INSERT INTO ... VALUES (..., ..., ...)" command. It implies that I have to add once all possible list-entries, am I wrong?

|||

Somehow I feel not comfortable with the "INSERT INTO ... VALUES (..., ..., ...)" command. It implies that I have to add once all possible list-entries, am I wrong?

Yes, you are wrong about that. In the previous posting, a sample table was created and it was populated with sample data. You DO NOT USE THE SAMPLE DATA -you use your own table and column names, following the example.

Create the MultiList function in your database, and then execute your 'standard SELECT' -carefully following the example.

|||

I think the way I'd like to format the data is a bit off... I just took a look at the raw data, and it appears to me that the problem isn't as complicated as I thought.

Table1 has a Parent-Child-hierarchy structure... Table2 connects Table1 and the Article_Table via the 'ArtNr'.

Code Snippet

Table1:

LfdNr Kriterium Einordnung

0 1 Marke

0 2 Markenhauptgruppe

0 3 Markenuntergruppe

1 1 Eigenmarke

2 1 Fremdmarke

2 2 Frische

2 3 Valensina

3 2 Handelsmarke

3 3 Chiquita

4 2 Paradise

4 3 Rio Doro

5 2 Lizenzmarke

5 3 Hitchcock

...

Code Snippet

Table2:

ArtNr LfdNr Kriterium

4711 2 1

4711 3 2

4711 4 3

5000 1 1

5000 2 2

...

The Output table should look like this:

Code Snippet

Desired Output:

ArtNr Marke Markenhauptgruppe Markenuntergruppe

4711 Fremdmarke Handelsmarke Rio Doro

5000 Eigenmarke Frische

|||

here it is, (replace the #table1 & #tabl2 with your orginal table name on the Sql Server 2000 & 2005 query)

Code Snippet

Create Table #table1 (

[LfdNr] int ,

[Kriterium] int ,

[Einordnung] Varchar(100)

);

Insert Into #table1 Values('0','1','Marke');

Insert Into #table1 Values('0','2','Markenhauptgruppe');

Insert Into #table1 Values('0','3','Markenuntergruppe');

Insert Into #table1 Values('1','1','Eigenmarke');

Insert Into #table1 Values('2','1','Fremdmarke');

Insert Into #table1 Values('2','2','Frische');

Insert Into #table1 Values('2','3','Valensina');

Insert Into #table1 Values('3','2','Handelsmarke');

Insert Into #table1 Values('3','3','Chiquita');

Insert Into #table1 Values('4','2','Paradise');

Insert Into #table1 Values('4','3','Rio Doro');

Insert Into #table1 Values('5','2','Lizenzmarke');

Insert Into #table1 Values('5','3','Hitchcock');

Create Table #table2 (

[ArtNr] Varchar(100) ,

[LfdNr] int ,

[Kriterium] int

);

Insert Into #table2 Values('4711','2','1');

Insert Into #table2 Values('4711','3','2');

Insert Into #table2 Values('4711','4','3');

Insert Into #table2 Values('5000','1','1');

Insert Into #table2 Values('5000','2','2');

Code Snippet

--SQL Server 2000

Select

[ArtNr]

,[Einordnung]

into #Temp

from

#table2 A

Join #table1 B on

A.[LfdNr]=B.[LfdNr]

And A.[Kriterium] = B.[Kriterium]

Order By

1,2

Alter table #Temp Add RowId int identity(1,1), GroupId int

Update #Temp

Set

GroupId = RowId - (Select Min(RowId) -1 From #Temp Sub Where Sub.[ArtNr] = #Temp.[ArtNr])

select

[ArtNr]

,Isnull(Max(Case When GroupId = 1 Then [Einordnung] End),'') as [Marke]

,Isnull(Max(Case When GroupId = 2 Then [Einordnung] End),'') as [Markenhauptgruppe]

,Isnull(Max(Case When GroupId = 3 Then [Einordnung] End),'') as [Markenuntergruppe]

from

#temp

Group By

[ArtNr]

Code Snippet

--SQL Server 2005

;With CTE

as

(

Select

[ArtNr]

,[Einordnung]

,Row_Number() Over (Partition By [ArtNr] Order By [ArtNr]) GroupId

from

#table2 A

Join #table1 B on

A.[LfdNr]=B.[LfdNr]

And A.[Kriterium] = B.[Kriterium]

)

Select

[ArtNr]

,Isnull([1] ,'') as [Marke]

,Isnull([2] ,'') as [Markenhauptgruppe]

,Isnull([3] ,'') as [Markenuntergruppe]

from

CTE

Pivot

(

Max([Einordnung]) For GroupId in ([1],[2],[3])

) Pvt

|||

Awesome, this is it! Many thanks! But I've got another problem:

(SSMS marks the '(' near PVT as Error?)

Meldung 102, Ebene 15, Status 1, Zeile 21

Falsche Syntax in der N?he von '('.

in english: ;-)

Message 102, Level 15, Status 1, Row 21

False Syntax near '('.

I don't know exactly what's wrong with the statement, to me it seems fine...

|||anyone?

No comments:

Post a Comment