Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to print same name with address on a A4 Lebel sheet of 12 Sticker from table.
I am using datalist,not able to fill all on 12 level, it shows only one time.It require to repeat 12 times.
SELECT * from Customer where CustID='101'
Suggest better solution.

What I have tried:

SELECT * from Customer where CustID='101'
Posted
Updated 1-Aug-18 3:58am
Comments
RedDk 11-Jul-18 18:20pm    
You want to print same name with address (which, considering the database indexed cutomer id contains those two items in distinct columns (say) "name" and "address") but you are using the asterisk (*) to return ALL column items (however many that might be) in your query?

How about this instead:

SELECT [name], [address] FROM Customer WHERE CustID = 101
Kornfeld Eliyahu Peter 12-Jul-18 4:18am    
Do not duplicate the data, but duplicate the visual elements...
LebneizTech 31-Jul-18 8:51am    
Dear Sir, I want to print name and address 12 times on a A4 Lebel sheet of 12 Sticker . Means same output with name and address 12 times.
Kornfeld Eliyahu Peter 1-Aug-18 9:05am    
Think about it... The label you want to display is a control - a single visual unit, bounded to some data... Now put 12 of that unit on your page and bind all of them to the same data? You need no 12 times the data!!!
CHill60 1-Aug-18 10:06am    
Virtual 5!

I don't recommend it at all - this is definitely something that you should be doing in your presentation language, not in SQL.

However, Solution 1 is wrong: it;'s very possible, if rather nasty.
Start by creating a table called Count12 - it needs a single INT column called Counter.
Fill it with 12 values:
Counter
1
2
3
4
5
6
7
8
9
10
11
12
Then it's a simple JOIN to get the same info 12 times:
SQL
SELECT a.Name, a.Address FROM Count12 b
JOIN MyTable a ON 1=1
If you make the Count table contain say 1000 rows, you can restrict the number of copies using WHERE:
SQL
SELECT a.Name, a.Email FROM Count12 b
JOIN MyTable a ON 1=1
WHERE b.Counter <= 5
Will give you 5 copies of each row for example.

You're still better off doing it in your presentation language though.
 
Share this answer
 
You need to use a CROSS JOIN against a list of numbers - See CP article Cross Join Introduction – Create Row Combinations[^]

For example: Generating a series for yourself ...
SQL
declare @Customer table (CustID int, CustName nvarchar(100))
insert into @Customer (CustID, CustName) values (101, 'My test data')


;with series as
(
	select 1 as datum
	UNION ALL
	select datum + 1
	from series where datum + 1 < 13
)
SELECT * from @Customer 
cross join series
where CustID=101
OR you can use the system tables to generate a sequence like this
SQL
SELECT * from @Customer 
CROSS JOIN (SELECT TOP (12) ROW_NUMBER() OVER (ORDER BY [object_id]) as datum FROM sys.all_objects) AS series
WHERE CustID=101
 
Share this answer
 
In my knowledge it's not possible because sql or mysql can not select same data multiple time because he want sometime unique b/w them it can be id, any text, or anything. For example i have five columns id, name, country, city, status and values are
1 John PK KHI 1
2 Smith PK KHI 1
3 John PK KHI 1
in this example you can get john twice but there is one this is unique b/w them is id. But it can't select john twice having id 1 even if you will filter it in the column selection.
If you were thinking about UNION it's also not possbile. Only one thing is possible do this outside sql or mysql like in php code or js code or .net code.
 
Share this answer
 
Comments
OriginalGriff 1-Aug-18 9:30am    
That turns out not to be the case ... it is perfectly possible, even easy. Not a good idea though.
CHill60 1-Aug-18 9:47am    
SQL can select the same data multiple times - see my solution
You could try using a temporary table with 12 (or however many you need) and join that with the customer table. I wrote you a stored procedure that should do it.

SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE A4REPEAT
	@CustId int = 0,
	@NumTimes int = 0
AS
BEGIN
	SET NOCOUNT ON;

        IF EXISTS(SELECT * FROM #temp) DROP TABLE #temp

	CREATE TABLE #temp (
	  CustId int
	)
	DECLARE @i int = 0
	WHILE @i < @NumTimes 
	BEGIN
		SET @i = @i + 1
		INSERT INTO #temp (CustId) VALUES (@CustId)
	END

	SELECT [#temp].[CustId], [FirstName], [LastName], [... address info ...] FROM [#temp] INNER JOIN [Customer] ON [#temp].[CustId]=[Customer].[CustId];

END
GO
 
Share this answer
 
Comments
CHill60 1-Aug-18 10:14am    
If #Temp does not already exist then this will throw an exception on
IF EXISTS(SELECT * FROM #temp) DROP TABLE #temp
Surely
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
would be better?

Also there are more elegant ways of creating a sequence table than using a WHILE loop. Using loops in SQL is bad practice
Mike V Baker 1-Aug-18 10:54am    
Thanks for the notes. I got both of those parts from other posts. If there's a more elegant way, please post it so we can all learn from it. [EDIT] - I see now that you already posted it in solution #3, must have been while I was testing mine. I think I like the last one that uses sys.all_objects.
CHill60 3-Aug-18 17:54pm    
Good to hear you were testing your solution! So many don't bother:-(
I often get caught out like that too so it looks like I'm just posting the same solution.
I too like the all objects... but there are limits to it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900