how to limit table row and display remaining data in next column in sql
-
My table data.
id clientname contactperson addr1 addr2
1 Jhon Mujeeb alld alld
2 Jhonny rahman abc Null
3 Sam aa aa aa
4 abc dd dd dd
5 Tin Ansari abc abc
6 Abc k Null Null
7 DEF k
8 GHI h
9 JKL y
10 MNO g
11 PQR hiI have a table name tbl_client
my querry is i want to display only client name Like Below(Column_name1) (Column_name2) (Column_name3) (Column_name4)
Jhon abc DEF MNO
jhonny Tin GHI PQR
Sam Abc JKLPlease help me in getting above result.
-
Do it like this, and let your presentation layer handle the format of the results, otherwise you'll run into problems like what happens when you have more than 12 rows in your source data.
SELECT clientname
FROM tbl_clientJohn
-
Dnt want to do it on design time and it wont be more than 12.
12 records only.
actually want to print an rdlc report that's y need the query.hence cannot design it's fixed.
Thank you. - abuzar_ansari123 - Monday, April 10, 2017 3:52 AM
This code will get the result that you are looking for
SELECT
[col1] = MAX(CASE WHEN (tc.id-1) /3 = 0 THEN tc.clientname ELSE '' END)
, [col2] = MAX(CASE WHEN (tc.id-1) /3 = 1 THEN tc.clientname ELSE '' END)
, [col3] = MAX(CASE WHEN (tc.id-1) /3 = 2 THEN tc.clientname ELSE '' END)
, [col4] = MAX(CASE WHEN (tc.id-1) /3 = 3 THEN tc.clientname ELSE '' END)
FROM tbl_client AS tc
GROUP BY (tc.id %3)
ORDER BY CASE WHEN (tc.id %3) = 0 THEN 3 ELSE (tc.id %3) END - DesNorton - Monday, April 10, 2017 4:18 AM
Thank you so much for the code. its working for me.
1 more question i have another table to get similar kind of data.
what i am trying tom do is suppose my tbl_client have 27 rows which i want to divide in 5 columns.
i just want 1 column value like column "clientname".
if i divide 27 by 5 i get 5.4 it should round to 6 which means 6 rows per columnwhat i have tried is below
declare @ccount decimal(18,1)=(
select count(clientname) as ClientCount from tbl_client);
print @ccount;
Declare @rownumber int=CEILING(@ccount/5);
print @rownumber;
SELECT
[col1] = MAX(CASE WHEN (tc.id-1) /@rownumber = 0 THEN tc.clientname ELSE null END)
, [col2] = MAX(CASE WHEN (tc.id-1) /@rownumber = 1 THEN tc.clientname ELSE null END)
, [col3] = MAX(CASE WHEN (tc.id-1) /@rownumber = 2 THEN tc.clientname ELSE null END)
, [col4] = MAX(CASE WHEN (tc.id-1) /@rownumber = 3 THEN tc.clientname ELSE null END)
, [col5] = MAX(CASE WHEN (tc.id-1) /@rownumber = 4 THEN tc.clientname ELSE null END)
FROM tbl_client AS tc
GROUP BY (tc.id %@rownumber)
ORDER BY CASE WHEN (tc.id %@rownumber) = 0 THEN 1 ELSE (tc.id %@rownumber) END
i am getting the output but there is null values in middle.
can i get all null values in last row.
can i get all null values in last row.
And Thank You Once Again For the code.
Basically I want All null values in last row.
and there can be n number of rows but column will be 5
suppose if there is 52 rows so 52/5 which is 10.4 round to 11 so 11 rows per column -
In your ORDER BY, you need to
replaceORDER BY CASE WHEN (tc.id %@rownumber) = 0 THEN 1 ELSE (tc.id %@rownumber) END
withORDER BY CASE WHEN (tc.id %@rownumber) = 0 THEN @rownumber ELSE (tc.id %@rownumber) ENDNOTE: This only works if id is a sequential number starting from 1. In other words, if there are any gaps in id then the code will not work.
The following adjustment will help ensure that missing numbers in id do not affect the output
WITH cteData AS (
SELECT
tc.clientname
, rn = ROW_NUMBER() OVER (ORDER BY tc.id)
FROM tbl_client AS tc
)
SELECT
[col1] = MAX(CASE WHEN (cte.rn-1) /@rownumber = 0 THEN cte.clientname ELSE null END)
, [col2] = MAX(CASE WHEN (cte.rn-1) /@rownumber = 1 THEN cte.clientname ELSE null END)
, [col3] = MAX(CASE WHEN (cte.rn-1) /@rownumber = 2 THEN cte.clientname ELSE null END)
, [col4] = MAX(CASE WHEN (cte.rn-1) /@rownumber = 3 THEN cte.clientname ELSE null END)
, [col5] = MAX(CASE WHEN (cte.rn-1) /@rownumber = 4 THEN cte.clientname ELSE null END)
FROM cteData AS cte
GROUP BY (cte.rn %@rownumber)
ORDER BY CASE WHEN (cte.rn %@rownumber) = 0 THEN @rownumber ELSE (cte.rn %@rownumber) END; - DesNorton - Monday, April 10, 2017 1:19 PM
Thank You Verryyy Much🙂:).
The code is working as per my needs.
Sir can just explain me these below 2 lines.
[col5] = MAX(CASE WHEN (cte.rn-1) /@rownumber = 4 THEN cte.clientname ELSE null END)
(cte.rn %@rownumber)And also can you please recommend me some use full books to learn and this kind of querry,
or if you have 1 can you please send it to my id abuzar_ansari123@yahoo.com .
Once Again Many thanks to you for helping me out.🙂 -
Let's start with the CTE (Common Tbale Expression)
Because there is no guarantee that there are no gaps in the value of id in tbl_Client (even if it is an IDENTITY field), we use ROW_NUMBER() to ensure that we ALWAYS have no gaps. As a bonus, if you change n = ROW_NUMBER() OVER (ORDER BY tc.id) to rn = ROW_NUMBER() OVER (ORDER BY tc.clientname), you will have your names ordered alphabetically, instead of by the order that they were inserted into the table.Now, if we only used [col5] = CASE WHEN (cte.rn-1) /@rownumber = 4 THEN cte.clientname ELSE null END, we would get a row for each value in the table, with the value in the correct column. This is done by diving the cte.rn by the number of rows. We then have to do a MAX() and GROUP BY, to ensure that we ignore all the NULL values, and get a single row for row required. The (cte.rn %@rownumber) is dividing cte.rn by @rownumber, and keeping the non-integer (left over) portion of the math.
-
Thanks for the detailed tips
-
Thank You so much for the information
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply