April 2, 2009 at 10:34 am
I would like to display some of the fields of two rows side by side but am struggling! The table is as follows:
CREATE TABLE [dbo].[ForecastPartsD](
[id] [nvarchar](50) NULL,
[description] [nvarchar](60) NULL,
[period] [nvarchar](3) NULL,
[fcyear] [nvarchar](5) NULL,
[units] [decimal](18, 0) NULL,
[include] [nvarchar](10) NULL,
[header] [nvarchar](50) NULL
) ON [PRIMARY]
Example data:
INSERT INTO dbo.ForecastPartsD (id, description, period, fcyear, units, include, header)
values ('A','A Desc','JAN','8/9',10, 'TRUE','A HEADER')
GO
INSERT INTO dbo.ForecastPartsD (id, description, period, fcyear, units, include, header)
values ('A','A Desc','JAN','8/9',20, 'TRUE','B HEADER')
So basically what I want the output to be like is:
A, A Desc, Jan, 8/9, 10 (units from A HEADER), 20 (units from B HEADER)
April 2, 2009 at 10:45 am
Funny, I was just going to post a question very similar to yours.
To use an example I found in a book, this is a family:
Date Name G Father Mother
-------- -------- --- ----------------- ---------------
5/19/22 James M James Halloway Kelly Halloway
8/05/28 Audry F Bryan Miller Karen Miller
8/19/51 Melanie F James Halloway Audry Halloway
8/30/53 James M James Halloway Audry Halloway
2/12/58 Dara F James Halloway Audry Halloway
3/13/61 Corwin M James Halloway Audry Halloway
3/13/65 Cameron M Richard Campbell Elizabeth Campbell
I need to bring back father, mother, and child, child, child all in one line, with commas in between. The children are on separate rows in the database. How can I get the three children on the same line with the mother and father, using T-SQL 2005. The number of children of course can be different for each group of parents. Stored procedures are fine to use. Ideas or suggestions would be VERY helpful!
Thanks!
Linda
April 2, 2009 at 10:51 am
Hi
Check out the below link this will solve your problem.
April 2, 2009 at 11:13 am
Thank you for the link. I think I can play around with the "medals" example to make it work.
Linda
April 2, 2009 at 11:16 am
Let us know if you have any questions.
April 2, 2009 at 11:21 am
David (4/2/2009)
I would like to display some of the fields of two rows side by side but am struggling! The table is as follows:
CREATE TABLE [dbo].[ForecastPartsD]([id] [nvarchar](50) NULL,
[description] [nvarchar](60) NULL,
[period] [nvarchar](3) NULL,
[fcyear] [nvarchar](5) NULL,
[units] [decimal](18, 0) NULL,
[include] [nvarchar](10) NULL,
[header] [nvarchar](50) NULL
) ON [PRIMARY]
Example data:
INSERT INTO dbo.ForecastPartsD (id, description, period, fcyear, units, include, header)values ('A','A Desc','JAN','8/9',10, 'TRUE','A HEADER')
GO
INSERT INTO dbo.ForecastPartsD (id, description, period, fcyear, units, include, header)
values ('A','A Desc','JAN','8/9',20, 'TRUE','B HEADER')
So basically what I want the output to be like is:
A, A Desc, Jan, 8/9, 10 (units from A HEADER), 20 (units from B HEADER)
You have to use PIVOT clause to convert rows into columns, for e.g.
SELECT [id], [A HEADER],
FROM ForecastPartsD
PIVOT( SUM( units ) FOR header IN( [A HEADER], ) ) P
If the list of columns are not known, then you can form the dynamic PIVOT sql by getting distinct headers and appending into a variable.
--Ramesh
April 2, 2009 at 2:57 pm
Thanks Guys but I'm still stuck! Here's a bit more info, firstly I've created a view that PIVOTs the data:
CREATE VIEW PivotData
AS
SELECT id, description, [JAN], [FEB], [MAR], header
FROM dbo.ForecastPartsD
PIVOT (SUM(units) FOR period IN ([JAN],[FEB],[MAR])) P
I've added more test data to demonstrate the end result:
INSERT INTO dbo.ForecastPartsD (id, description, period, fcyear, units, include, header)
values ('A','A Desc','JAN','8/9',10, 'TRUE','A HEADER')
GO
INSERT INTO dbo.ForecastPartsD (id, description, period, fcyear, units, include, header)
values ('A','A Desc','JAN','8/9',20, 'TRUE','B HEADER')
GO
INSERT INTO dbo.ForecastPartsD (id, description, period, fcyear, units, include, header)
values ('A','A Desc','FEB','8/9',10, 'TRUE','A HEADER')
GO
INSERT INTO dbo.ForecastPartsD (id, description, period, fcyear, units, include, header)
values ('A','A Desc','FEB','8/9',20, 'TRUE','B HEADER')
GO
INSERT INTO dbo.ForecastPartsD (id, description, period, fcyear, units, include, header)
values ('A','A Desc','MAR','8/9',10, 'TRUE','A HEADER')
GO
INSERT INTO dbo.ForecastPartsD (id, description, period, fcyear, units, include, header)
values ('A','A Desc','MAR','8/9',20, 'TRUE','B HEADER')
Now the following produces exactly what I want but there has to be a better way of doing it:
SELECT A.id, A.description, A.JAN AS [A JAN],
B.JAN AS , A.FEB AS [A FEB],
B.FEB AS , A.MAR AS [A MAR],
B.MAR AS
FROM
(SELECT id, description, JAN, FEB, MAR
FROM PivotData
WHERE header = 'A HEADER') A
JOIN
(SELECT id, description, JAN, FEB, MAR
FROM PivotData
WHERE header = 'B HEADER') B ON B.id = A.id
April 3, 2009 at 1:23 am
Here is another way to do it....
SELECT id, description,
[JAN A HEADER] AS [A JAN], [JAN B HEADER] AS ,
[FEB A HEADER] AS [A FEB], [FEB B HEADER] AS ,
[MAR A HEADER] AS [A MAR], [MAR B HEADER] AS
FROM (
SELECT id, description, units, period + ' ' + header AS periodheader
FROM dbo.ForecastPartsD
) A
PIVOT
(
SUM( units )
FOR periodheader IN( [JAN A HEADER], [FEB A HEADER], [MAR A HEADER],
[JAN B HEADER], [FEB B HEADER], [MAR B HEADER] )
) P
--Ramesh
April 3, 2009 at 2:35 am
Thanks for your help Ramesh
April 3, 2009 at 2:45 am
I'm glad I'd helped you.
--Ramesh
April 3, 2009 at 3:29 am
Hi Ramesh,
Slightly different question this time. Using the following:
CREATE TABLE [dbo].[Test](
[name] [varchar](10) NOT NULL,
[jan] [int] NOT NULL,
[feb] [int] NOT NULL,
[mar] [int] NOT NULL,
[year] [int] NOT NULL)
INSERT INTO [dbo].[Test] (name, jan, feb, mar, year)
VALUES ('Bob',10,15,20,8)
GO
INSERT INTO [dbo].[Test] (name, jan, feb, mar, year)
VALUES ('Bob',15,20,25,9)
How can I produce a result set such as:
Bob, 10, 15, 20, 15, 20, 25
In other words what I now want to do is to show a rolling view of the years, in my example I've only used Jan to Mar but in reality it will be Jan to Dec. The number of years is not fixed i.e. it could be two or more years that need to be displayed.
April 3, 2009 at 8:22 am
Hi
Is this a good way of having all the possible values
FOR periodheader IN( [JAN A HEADER], [FEB A HEADER], [MAR A HEADER],
[JAN B HEADER], [FEB B HEADER], [MAR B HEADER] )
April 3, 2009 at 8:23 am
HI david,
Did you tried to use COALESCE function which i had provided yesterday????
April 3, 2009 at 10:41 am
Vijaya Kadiyala (4/3/2009)
HiIs this a good way of having all the possible values
FOR periodheader IN( [JAN A HEADER], [FEB A HEADER], [MAR A HEADER],[JAN B HEADER], [FEB B HEADER], [MAR B HEADER] )
What's wrong with having all possible values when you already knew the possible values it can hold? Apart from it, is there any other way of doing it other than using cross tabs? If yes, can you please show us, so that we can learn something new from you?
--Ramesh
April 3, 2009 at 10:48 am
David (4/3/2009)
Hi Ramesh,Slightly different question this time. Using the following:
CREATE TABLE [dbo].[Test]([name] [varchar](10) NOT NULL,
[jan] [int] NOT NULL,
[feb] [int] NOT NULL,
[mar] [int] NOT NULL,
[year] [int] NOT NULL)
INSERT INTO [dbo].[Test] (name, jan, feb, mar, year)
VALUES ('Bob',10,15,20,8)
GO
INSERT INTO [dbo].[Test] (name, jan, feb, mar, year)
VALUES ('Bob',15,20,25,9)
How can I produce a result set such as:
Bob, 10, 15, 20, 15, 20, 25
In other words what I now want to do is to show a rolling view of the years, in my example I've only used Jan to Mar but in reality it will be Jan to Dec. The number of years is not fixed i.e. it could be two or more years that need to be displayed.
As I mentioned earlier, if the no. of pivot values is not known or dynamic, you have to use dynamic SQL to form the required query to achieve the respective results.
Here are few links that will get you started..
http://www.sqlservercentral.com/articles/T-SQL/63681/%5B/url%5D
http://www.sqlservercentral.com/articles/cross+tab/65048/%5B/url%5D
--Ramesh
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply