I have the following problem:
I'd like to display these rows as columns via an sql-statement:
Input
Code Snippet
4 10000000 Juice4 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');
|||
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?
Jim R wrote: