show single field across several fields in record set?
-
I have a table where I query one of the fields. my question is, is there a way I can produce the results across 3 fields? I'd like to use a datagrid on a webpage with 3 columns, and fill the datagrid with the record set starting at the first column with the first result, then the second column with the second result, then the third column with the third result, then the first column second row gets the fourth result and so on. I'm assuming I need to populate a tempory table which has 3 columns with the data from my table? but I'm not sure how.
my table would be populated like;
field1
item1
item2
item3
item4
item5
item6
item7
...... and so on
the desired record set as shown in my datagrid would look like
Field1 Field2 Field3
item1 item2 item3
item4 item5 item6
item7 ....... ........
-
I've worked it out and I've included it below, unless someone can show me a better way?
CREATE TABLE #Results (RowID int identity(1,1), Col1 varchar(max),col2 varchar(max), col3 varchar(max))
declare @counter as int
declare @sCol1 Varchar(50)
declare @sCol2 Varchar(50)
declare @sCol3 Varchar(50)
set @counter = 1
declare @TmpName as varchar (max)
DECLARE CUR1 CURSOR FAST_FORWARD FOR
SELECT DISTINCT [CD Title]
FROM [Club CDs].dbo.[Club CDs]
ORDER BY [CD Title]
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @TmpName
set @sCol1 = @TmpName
set @counter = @counter+1
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM CUR1 INTO @TmpName
print @TmpName
if @counter = 1
begin
set @sCol1 = @TmpName
end
if @counter = 2
begin
set @sCol2 = @TmpName
end
if @counter = 3
begin
set @sCol3 = @TmpName
set @counter = 0
insert into #Results ([Col1],[col2],[col3]) values (@sCol1,@sCol2,@sCol3)
end
set @counter = @counter+1
END
CLOSE CUR1
DEALLOCATE CUR1
SELECT * FROM #Results
drop table #Results
-
Try not to use SQL Server's resources for this type of thing. Do it on the client side, something like this:
//This assumes you have already populated a DataTable named
//dataTableFromSQL, and the first column contains the values you want
//clientSideTable is the DataTable that the DataGrid will be bound to
DataTable clientSideTable = new DataTable();
clientSideTable.Columns.Add(new DataColumn("Col1", typeof(string)));
clientSideTable.Columns.Add(new DataColumn("Col2", typeof(string)));
clientSideTable.Columns.Add(new DataColumn("Col3", typeof(string)));
//initial newRow is used by the first row of values
DataRow newRow = clientSideTable.NewRow();
int columnIndex = 0;
for (int rowIndex = 0; rowIndex <= dataTableFromSQL.Rows.Count - 1; rowIndex++) {
string nextValue = dataTableFromSQL.Rows[rowIndex][0].ToString();
newRow[columnIndex] = nextValue;
columnIndex++;
//If all 3 columns have been populated, add the row to the table and create a new row
if ((rowIndex + 1) % 3 == 0) {
clientSideTable.Rows.Add(newRow);
newRow = clientSideTable.NewRow();
columnIndex = 0;
}
}
//We have exited the for loop, but the last row may not have been added to the table yet
if (columnIndex > 0)
clientSideTable.Rows.Add(newRow);
-
mick burden (3/9/2013)
I've worked it out and I've included it below, unless someone can show me a better way?Use the natural loops (pseudo cursors) found in every SELECT to number the rows and then pivot the rows mathematically using a classic Cross Tab (my favorite) or a PIVOT function. Like this...
--===== Create the test table and populate it on-the-fly.-- This is NOT a part of the solution.
SELECT d.Field1
INTO #Yourtable
FROM (
SELECT 'item1' UNION ALL
SELECT 'item2' UNION ALL
SELECT 'item3' UNION ALL
SELECT 'item4' UNION ALL
SELECT 'item5' UNION ALL
SELECT 'item6' UNION ALL
SELECT 'item7'
)d(Field1)
;
--===== Pivot the data with math instead of a While Loop
WITH
cteEnumerate AS
(
SELECT ItemNumber = ROW_NUMBER() OVER (ORDER BY Field1)-1,
Field1
FROM #YourTable
),
ctePrePivot AS
(
SELECT RowNum = ItemNumber/3,
ColNum = ItemNumber%3,
Field1
FROM cteEnumerate
) --=== Classic Cross Tab pivots the data mathematically
SELECT Field1 = MAX(CASE WHEN ColNum = 0 THEN Field1 ELSE '' END),
Field2 = MAX(CASE WHEN ColNum = 1 THEN Field1 ELSE '' END),
Field3 = MAX(CASE WHEN ColNum = 2 THEN Field1 ELSE '' END)
FROM ctePrePivot
GROUP BY RowNum
ORDER BY RowNum
;
For more information on how Cross Tabs work, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
thanks both of you, I give both a try and see what's easier, although I think I'd rather it was done inside a SP rather than clientside
-
Rick Krueger (3/9/2013)
Try not to use SQL Server's resources for this type of thing. Do it on the client side, something like this:I couldn't agree more. It's a great way to remove some of the load from the server.
Just remember that not everything has a "client side" to it and when that happens, you'll need to know how to do it using as few resources and as much performance as possible on the server side.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply