Click here to Skip to main content
15,119,363 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 4: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.
   
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
   
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.
   
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
   
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