June 24, 2011 at 9:12 am
In case we have one column with data but we want to get a view of that data in 3 columns for instance, this is useful in case we have a huge list of codes and we want to print them just to check them. (Like multicolumns display feature of Microsoft Word..)
04730
04731
04732
04733
04734
04735
04736
04737
04738
Output should be:
04730 04731 04732
04733 04734 04735
04736 04737 04738
Or:
04730 04733 04736
04731 04734 04737
04732 04735 04738
Thans a lot in advance,
G.
June 24, 2011 at 9:59 am
That really is a formatting issue which is best left to the front end. Let whatever you are using to pull the data format it for you instead of trying to make sql do it. It will not only be tons easier it will also most likely be faster.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2011 at 10:40 am
This one looks like homework. I agree with Sean that things like this should be left to the presentation layer. That said, if this is homework or your hands are tied to do this in T-SQL and you want some help please follow this article[/url] and post some proper DDL and DML so we can build your tables on our machines. Also, probably most importantly, please post what queries you have tried so far and we'll help you arrive at a working solution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 27, 2011 at 9:23 am
Estimated friends, I appreciate your concern, but trust me best practice to help in the forum is do not issue banal views when the answer is not known. I do know what to do on each layer and trust me you both are wrong, no other data engine, or layer, linq, entity framework, Datasets loops or whatever, nothing is faster than Queries with the adecuate plan in the SQL Server engine, which is optimized for large data processing and is the first member of the chain. Even more I did not mention I have further layer, the issue is as is.
Appropiate data to accomplish the current topic is any table with a primary user key. Anyway this would help, as I said I have one column and I need data in five columns for instance:
DECLARE @Products TABLE(ID VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO @Products(ID)
SELECT '04737' UNION ALL
SELECT '04738' UNION ALL
SELECT '04739' UNION ALL
SELECT '04740' UNION ALL
SELECT '04700' UNION ALL
SELECT '04787' UNION ALL
SELECT '04562' UNION ALL
SELECT '35892' UNION ALL
SELECT '58930' UNION ALL
SELECT '91880' UNION ALL
SELECT '94740' UNION ALL
SELECT '94741' UNION ALL
SELECT '96656' UNION ALL
SELECT '96757';
HOW TO SELECT ???
To obtain:
Column1 Column2 Column3 Column4 Column5
04737 04740 04562 91880 96656
04738 04700 35892 94740 96757
04739 04787 58930 94741
Thanks again,
L.
June 27, 2011 at 9:26 am
What's the condition that defines how the datas goes into what column?
June 27, 2011 at 9:37 am
I do know what to do on each layer and trust me you both are wrong, no other data engine, or layer, linq, entity framework, Datasets loops or whatever, nothing is faster than Queries with the adecuate plan in the SQL Server engine, which is optimized for large data processing and is the first member of the chain.
I disagree 100%. There is no way that you can possibly sit there and tell me that using a cursor and nested while loops in t-sql is faster than looping through those same rows in an application. If you actually believe that you really should do some tests on even somewhat large datasets (10,000+ rows).
It is interesting that you can blatantly tell both of us that we are wrong and point out that we don't understand sql server with its strengths and limitations. Then turn around and ask us for help.
Why do you think that application development has been separated into multiple layers over the last decade? Each level of the application has its own strengths and weaknesses. sql is awesome at data storage and retrieval but it sucks at presentation. That is why there is a presentation layer. The format of the data you are trying to achieve is RBAR which is horrible for performance. Retrieve your data into a datatable, then loop through all the rows and parse out the columns into the format you desire it in.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2011 at 9:48 am
Sean Lange (6/27/2011)
I do know what to do on each layer and trust me you both are wrong, no other data engine, or layer, linq, entity framework, Datasets loops or whatever, nothing is faster than Queries with the adecuate plan in the SQL Server engine, which is optimized for large data processing and is the first member of the chain.
I disagree 100%. There is no way that you can possibly sit there and tell me that using a cursor and nested while loops in t-sql is faster than looping through those same rows in an application. If you actually believe that you really should do some tests on even somewhat large datasets (10,000+ rows).
It is interesting that you can blatantly tell both of us that we are wrong and point out that we don't understand sql server with its strengths and limitations. Then turn around and ask us for help.
Why do you think that application development has been separated into multiple layers over the last decade? Each level of the application has its own strengths and weaknesses. sql is awesome at data storage and retrieval but it sucks at presentation. That is why there is a presentation layer. The format of the data you are trying to achieve is RBAR which is horrible for performance. Retrieve your data into a datatable, then loop through all the rows and parse out the columns into the format you desire it in.
Sean I think you misread his statement.
June 27, 2011 at 9:54 am
Ninja's_RGR'us (6/27/2011)
Sean Lange (6/27/2011)
I do know what to do on each layer and trust me you both are wrong, no other data engine, or layer, linq, entity framework, Datasets loops or whatever, nothing is faster than Queries with the adecuate plan in the SQL Server engine, which is optimized for large data processing and is the first member of the chain.
I disagree 100%. There is no way that you can possibly sit there and tell me that using a cursor and nested while loops in t-sql is faster than looping through those same rows in an application. If you actually believe that you really should do some tests on even somewhat large datasets (10,000+ rows).
It is interesting that you can blatantly tell both of us that we are wrong and point out that we don't understand sql server with its strengths and limitations. Then turn around and ask us for help.
Why do you think that application development has been separated into multiple layers over the last decade? Each level of the application has its own strengths and weaknesses. sql is awesome at data storage and retrieval but it sucks at presentation. That is why there is a presentation layer. The format of the data you are trying to achieve is RBAR which is horrible for performance. Retrieve your data into a datatable, then loop through all the rows and parse out the columns into the format you desire it in.
Sean I think you misread his statement.
I read it how Sean read it and while it was a snarky answer it was pretty funny, as was Sean's response. Way to ask for help crackbridge 🙂
It looks like you could use a CTE with a row_number() and then a cross tab to pivot those results using a mod operator...if you answer Ninja's earlier question we can provide you with tested code.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 27, 2011 at 10:06 am
What course are you taking?:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 27, 2011 at 10:34 am
Look friends, I'm sorry if someone felt wrong it was just in save of time and richness of knowledge.
I'll be shorter, what I say, forum is not to post too many ways of form but content. Issue was pretty clear, and long paragraphs of philosophy and history, give nothing.
I would naver say "to loop a cursor", this is never going to be a propper query plan nor planning, and that is probably what you would do in further slower layers.
About the data criteria, how to select how to fit in each column, is just beacuse of its order.
Record 1 goes to Row 1 Column 1
Record 2 goes to Row 1 Column 2
..
Record 5 goes to Row 1 Column 5
Record 6 goes to Row 2 Column 1
..
This is ordered by rows, but could also be by columns as stated in the first post sample of data.
I tried with CTE and rownumber but I've got independant rows for each column.
That's all. Hope we can get it!.
June 27, 2011 at 10:38 am
What problem are you trying to solve?
What is the business objective?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 27, 2011 at 10:44 am
The Business goal as stated at the beginning is for a huge list of codes to be able to print them in columns, to save paper, to build cheklists, just to check then.
Thanks,
L.
June 27, 2011 at 10:52 am
SELECT
O.Name AS tblName
, C.Name AS ColName
-- build a matrix, needs (X,Y) position
, ( ROW_NUMBER() OVER ( PARTITION BY O.object_id ORDER BY C.Name ) - 1 ) / 5 AS RID
, ( ROW_NUMBER() OVER ( PARTITION BY O.object_id ORDER BY C.Name ) - 1 ) % 5 AS Colid
FROM
sys.objects O
INNER JOIN sys.columns C
ON O.object_id = C.object_id
ORDER BY
tblName
, ColName
June 27, 2011 at 10:52 am
I was just curious as to what the codes represent and what the significance of presenting them in the format that you specified?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 27, 2011 at 10:58 am
... forgot the pivot :
SELECT
tblName--, RID
, MAX(CASE WHEN dtMx.Colid = 1 THEN ColName
ELSE NULL
END) AS COl1
, MAX(CASE WHEN dtMx.Colid = 2 THEN ColName
ELSE NULL
END) AS COl2
, MAX(CASE WHEN dtMx.Colid = 3 THEN ColName
ELSE NULL
END) AS COl3
, MAX(CASE WHEN dtMx.Colid = 4 THEN ColName
ELSE NULL
END) AS COl4
, MAX(CASE WHEN dtMx.Colid = 5 THEN ColName
ELSE NULL
END) AS COl5
FROM
(
SELECT
O.Name AS tblName
, C.Name AS ColName
-- build a matrix, needs (X,Y) position
, ( ROW_NUMBER() OVER ( PARTITION BY O.object_id ORDER BY C.Name ) - 1 )
/ 5 AS RID
, ( ROW_NUMBER() OVER ( PARTITION BY O.object_id ORDER BY C.Name ) - 1 )
% 5 + 1 AS Colid
FROM
sys.objects O
INNER JOIN sys.columns C
ON O.object_id = C.object_id
--ORDER BY
-- tblName
-- , ColName
) dtMx
GROUP BY
tblName
, RID
ORDER BY
tblName
, RID
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply