November 14, 2009 at 3:17 pm
Hi friends...
I event don't know the name of what I am trying to do to google it.. I'm using sql2005 (or 2008) and here's the problem: 3 tables must be come together to form the table I wrote below..
SOURCE TABLES:
Productstable
----------------
beer
keyboard
chair
SalesPointstable
------------------
newyork
berlin
moscov
zurich
SalesPricesTable
--------------------------------
beer..........newyork..4..USD
beer..........berlin...5..EURO
beer..........moscov...6..ROUBLE
beer..........zurich...7..FRANK
keyboard......newyork..6..USD
keyboard......berlin...7..EURO
keyboard......moscov...8..ROUBLE
keyboard......zurich...7..FRANK
chair.........newyork..6..USD
chair.........berlin...7..EURO
chair.........moscov...8..ROUBLE
chair.........zurich...6..FRANK
I NEED THIS RESULT TABLE:
..........newyork....berlin...moscov....zurich
beer........4.USD....5.EURO..6.ROUBLE...7.FRANK
keyboard....6.USD....7.EURO..8.ROUBLE...7.FRANK
chair.......6.USD....7.EURO..8.ROUBLE...6.FRANK
what can be the query ? I could not do it with pivot commands nor I could not designed this query.
any idea would be great. best regards.
November 14, 2009 at 3:58 pm
Why didn't PIVOT work?
Example:
SELECT prod, [newyork] AS 'newyork',[berlin] AS 'berlin',[moscov] AS 'moscov',[zurich] AS 'zurich'
FROM
(SELECT prod,point,price
FROM SalesPricesTable) p
PIVOT
(
MAX (price)
FOR point IN
( [newyork],[berlin],[moscov],[zurich] )
) AS pvt
ORDER BY prod
Another way would be the CrossTab or DynamicCrossTab solution. For details please see the link in my signature.
Also, please follow the first link in my signature on how to post sample data in order to get verified results (I had to make some wild guess regarding your table structure...).
November 15, 2009 at 4:38 am
my friend,
pivot didnt work because it can ony accept aggregate function. I also need money indormation (euro, usd, etc...) I did run pivot but could not append USD nor EURO to it. that makes 2 columns of pivot information. price and money information. also pivot did not accept string functions.
November 15, 2009 at 4:55 am
Obviously, I've been wrong in guessing your table structure then...
Before I give it a second try I kindly ask you to provide sample data in a ready to use format as described in the first link in my signature.
Alternatively you can do some research on CrossTab and DynamicCrossTab. I have two links in my sig for that as well.
November 15, 2009 at 5:17 am
my friend, here's the db for you:
http://www.karina-mira.com/testdb.rar
OR here's the script for tables & data. please create database "test" before ececutiong. I did not included database creation script codes:
thank you for your time and effort.
USE [test]
CREATE TABLE [dbo].[SalesPrices](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NULL,
[SalesPointID] [int] NULL,
[Price] [float] NULL,
[Money] [nvarchar](50) NULL,
CONSTRAINT [PK_SalesPrices] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[SalesPrices] ON
INSERT [dbo].[SalesPrices] ([ID], [ProductID], [SalesPointID], [Price], [Money]) VALUES (1, 1, 1, 10, N'USD')
INSERT [dbo].[SalesPrices] ([ID], [ProductID], [SalesPointID], [Price], [Money]) VALUES (2, 2, 1, 11, N'USD')
INSERT [dbo].[SalesPrices] ([ID], [ProductID], [SalesPointID], [Price], [Money]) VALUES (3, 3, 1, 12, N'USD')
INSERT [dbo].[SalesPrices] ([ID], [ProductID], [SalesPointID], [Price], [Money]) VALUES (4, 1, 2, 13, N'EURO')
INSERT [dbo].[SalesPrices] ([ID], [ProductID], [SalesPointID], [Price], [Money]) VALUES (5, 2, 2, 14, N'EURO')
INSERT [dbo].[SalesPrices] ([ID], [ProductID], [SalesPointID], [Price], [Money]) VALUES (6, 3, 2, 15, N'EURO')
INSERT [dbo].[SalesPrices] ([ID], [ProductID], [SalesPointID], [Price], [Money]) VALUES (7, 1, 3, 16, N'ROUBLE')
INSERT [dbo].[SalesPrices] ([ID], [ProductID], [SalesPointID], [Price], [Money]) VALUES (8, 2, 3, 17, N'ROUBLE')
INSERT [dbo].[SalesPrices] ([ID], [ProductID], [SalesPointID], [Price], [Money]) VALUES (9, 3, 3, 18, N'ROUBLE')
INSERT [dbo].[SalesPrices] ([ID], [ProductID], [SalesPointID], [Price], [Money]) VALUES (10, 1, 4, 19, N'FRANK')
INSERT [dbo].[SalesPrices] ([ID], [ProductID], [SalesPointID], [Price], [Money]) VALUES (11, 2, 4, 20, N'FRANK')
INSERT [dbo].[SalesPrices] ([ID], [ProductID], [SalesPointID], [Price], [Money]) VALUES (12, 3, 4, 21, N'FRANK')
SET IDENTITY_INSERT [dbo].[SalesPrices] OFF
/****** Object: Table [dbo].[SalesPoints] Script Date: 11/15/2009 14:15:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SalesPoints](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SalesPointName] [nvarchar](50) NULL,
CONSTRAINT [PK_SalesPoints] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[SalesPoints] ON
INSERT [dbo].[SalesPoints] ([ID], [SalesPointName]) VALUES (1, N'NewYork')
INSERT [dbo].[SalesPoints] ([ID], [SalesPointName]) VALUES (2, N'Berlin')
INSERT [dbo].[SalesPoints] ([ID], [SalesPointName]) VALUES (3, N'Moscow')
INSERT [dbo].[SalesPoints] ([ID], [SalesPointName]) VALUES (4, N'Zurich')
SET IDENTITY_INSERT [dbo].[SalesPoints] OFF
/****** Object: Table [dbo].[Products] Script Date: 11/15/2009 14:15:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](50) NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Products] ON
INSERT [dbo].[Products] ([ID], [ProductName]) VALUES (1, N'Beer')
INSERT [dbo].[Products] ([ID], [ProductName]) VALUES (2, N'Keyboard')
INSERT [dbo].[Products] ([ID], [ProductName]) VALUES (3, N'Chair')
SET IDENTITY_INSERT [dbo].[Products] OFF
November 15, 2009 at 5:55 am
The "trick" is to concatenate the two columns Price and Money and treat it as one column inside the PIVOT statement:
SELECT ProductName, [NewYork] , [Berlin], [Moscow], [Zurich]
FROM
(SELECT
ProductName,
SalesPointName,
CAST (Price as CHAR(2)) + ' ' + Money AS Value
FROM products pr
INNER JOIN salesprices spr ON pr.ID = spr.ProductId
INNER JOIN salespoints spo ON spo.ID = spr.SalesPointID) p
PIVOT
(
MAX (Value)
FOR SalesPointName IN
( [NewYork], [Berlin], [Moscow], [Zurich] )
) AS pvt
ORDER BY ProductName
/* result set
ProductNameNewYorkBerlinMoscowZurich
Beer10 USD13 EURO16 ROUBLE19 FRANK
Chair12 USD15 EURO18 ROUBLE21 FRANK
Keyboard11 USD14 EURO17 ROUBLE20 FRANK*/
November 15, 2009 at 6:05 am
WOOOWWW.... that was fast...
You saved my day man... if somebody can say "I know SQL" then he must be you.:-P
thank you, thank you and thank you...
November 15, 2009 at 7:10 am
Your very welcome.
Did you notice how fast you got an answer once you provided ready to use sample data?
And no, I cannot say "I know SQL". I know just enough to get around... But I can say: "I'm part of a great community: SSC (SQLServerCentral)." And so can you. 😉
Welcome!
November 15, 2009 at 11:25 am
If I may add a few comments:
1. Lutz knows SQL. He is humble enough to admit that he is still capable of learning more about it, but then we all learn more by hanging out here. SSC really is a great community and an invaluable resource for improving T-SQL and dba skills.
2. He wasn't kidding about taking time to set up the problem being worthwhile. It really does get you coded and tested answers much faster. Thank you for listening to his advice and taking the time to redo it. We all love to help people who are courteous, cooperative, and willing to their own share of the work.
3. A most excellent discussion of the problem of UNPIVOT with multiple columns took place here, if either of you care to read some more.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 16, 2009 at 8:32 am
of course I'll read it my friend, thanks...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply