July 4, 2013 at 1:12 am
Hi,
I have the following table & (sample) associated data set:
CREATE TABLE TableHeaders
(
Period_Year int,
Period_Month varchar(255),
ColumnID int,
ColumnLabel varchar(255)
);
---
insert into TableHeaders values (2012, 'January', 1, 'Jan-12')
insert into TableHeaders values (2012, 'January', 2, 'RAF')
insert into TableHeaders values (2012, 'January', 3, 'STP')
insert into TableHeaders values (2012, 'January', 4, 'PY')
insert into TableHeaders values (2012, 'January', 5, 'Jan-12')
insert into TableHeaders values (2012, 'January', 6, 'RAF')
insert into TableHeaders values (2012, 'January', 7, 'STP')
---
/* sample data set
Period_YearPeriod_MonthColumnIDColumnLabel
2012January1Jan-12
2012January2RAF
2012January3STP
2012January4PY
2012January5Jan-12
2012January6RAF
2012January7STP
*/
I want to select from the table
SELECT ColumnLabel
FROM TableHeaders
WHERE Period_Year = @pv_SelectedYear
AND Period_Month = @pv_SelectedMonth
ORDER BY ColumnID
but instead of getting a data set giving a number of rows (from example it would be the list of columnLabels), I would like to get back one row with as many columns as they are rows.
From example, I would like my data set to be:
Col1col2col3col4col5col6col7
Jan-12RAFSTPPYJan-12RAFSTP
(column names will not be used so can be anything)
I hope the above makes sense - annoyingly, I can test things out out as I am "sqlserver-less" just now :ermm:
Cheers,
bleroy
July 4, 2013 at 1:22 am
A simple example of the PIVOT instruction exists on the MSDN. Have a play with that and let us know how you manage when you get the chance:
http://msdn.microsoft.com/en-us/library/ms177410(v=SQL.105).aspx
July 4, 2013 at 2:35 am
Thanks Braindonor - the issue is that I don't have access to a sql server just now so I little stuck when it comes to trying stuff out :crying:
I was also thinking about using the PIVOT operator but because there are no aggregation to perform, and no natural row label to use, I am not convinced it makes sense.
tbh, I was going to simply go the stored proc route, and create myself a #table containing as many columns as there are rows returned by the source query (using cursor/RBAR :cool:) and then do a "select *" from that #table ... essentially something like:
CREATE TABLE #tblHeaders(ColumnLabel1 nvarchar(50));
DECLARE @ColIndex Int
DECLARE @NewColName nvarchar(50)
DECLARE @name nvarchar(50)
SET @ColIndex = 1;
DECLARE db_cursor CURSOR FOR
SELECT ColumnLabel FROM TableHeaders
WHERE Period_Year = 2012
AND Period_Month = 'January'
ORDER BY ColumnID;
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ColIndex = 1
BEGIN
INSERT INTO #tblHeaders values (@name);
SET @ColIndex = @ColIndex + 1;
END
IF @ColIndex > 1
SET @NewColName = 'ColumnLabel' + @ColIndex;
ALTER table #tblHeaders
add @NewColName nvarchar(max);
UPDATE #tblHeaders
SET @NewColName = @name;
SET @ColIndex = @ColIndex + 1;
END
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
SET @ColIndex = 1;
SELECT * FROM #tblHeaders;
Again - I can't run/compile this at all, so I may have typos/stupid syntax, random semicolons, etc - apologies if these escaped my attention! (reason for being dim no 2: I have also been doing mainly pl/sql for the last year)
Would the above work? and is there a better way to do it? (keeping in mind that there will 'never' be more than circa 10 rows to loop through.
Cheers,
B
July 4, 2013 at 2:59 am
You can run SQL queries and get result on the fly
July 4, 2013 at 3:25 am
thanks for that link!
Thinking of it, I do need to get it to work as a single sql SELECT statement as opposed to SP, so I'll have a go on there.
Cheers,
B
July 4, 2013 at 3:59 am
Rather than using a Pivot you could use a CROSS TAB method
SELECT
MAX(CASE ColumnId
WHEN 1 then Value
ELSE NULL
END) Col1
MAX(CASE ColumnId
WHEN 2 then Value
ELSE NULL
END) Col2
MAX(CASE ColumnId
WHEN 3 then Value
ELSE NULL
END) Col3
MAX(CASE ColumnId
WHEN 4 then Value
ELSE NULL
END) Col4
MAX(CASE ColumnId
WHEN 5 then Value
ELSE NULL
END) Col5
From
MyTable
If you have a specific requirement for period then you can group by the first two columns and you should get the results by month.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
July 4, 2013 at 4:38 am
This does do the trick! thanks for that 🙂
I would still love to have a method using SELECT only (i.e. not a SP) that would work regardless of the number of rows (i.e. with this solution, I need to update the SQL SELECT statement if I were to add column labels) but I'm not sure that's even possible.
btw - using sqlfiddle.com made me realise that my code is stuffed with errors (e.g. forgot "FROM mytable" in the first SELECT statement! :blush:) - will edit.
Cheers,
B
July 4, 2013 at 10:29 am
bleroy (7/4/2013)
I would still love to have a method using SELECT only (i.e. not a SP) that would work regardless of the number of rows (i.e. with this solution, I need to update the SQL SELECT statement if I were to add column labels) but I'm not sure that's even possible.
The second link that Luis provided will show you exactly how to do that and it's a pretty simple technique. The length of the article is due to the explanations for why each and every step works the way it does.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 4, 2013 at 4:08 pm
Hi Jeff,
I read the article after receiving those links and it is very good stuff and would indeed respond to my requirement very well - thank you very much for sharing it!
Kindest regards,
B
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply