declare @Stage table (i int identity(1,1), InputName varchar(50), InputValue varchar(50))
insert into @Stage (InputName, InputValue)
	select 'A', '1' union select 'A', '2' union
	select 'B', '3' union select 'B', '4' union
	select 'C', '5'

-- desired resultset 
-- (Iteration, InputName, InputValue)
--	1, A, 1
--	1, B, 3
--	1, C, 5
--	2, A, 1
--	2, B, 4
--	2, C, 5
--	3, A, 2
--	3, B, 3
--	3, C, 5
--	4, A, 2
--	4, B, 4
--	4, C, 5

-- if 'D', '7' was added, then:
--	1, A, 1
--	1, B, 3
--	1, C, 5
--	1, D, 7
--	2, A, 1
--	2, B, 4
--	2, C, 5
--	2, D, 7
--	3, A, 2
--	3, B, 3
--	3, C, 5
--	3, D, 7
--	4, A, 2
--	4, B, 4
--	4, C, 5
--	4, D, 7

     insert into @Stage (InputName, InputValue)
	select 'Email', 'yak@yak.com' union select 'Email', 'test@test.com' union
	select 'UserId', '3' union select 'UserId', '4' union
	select 'Password', '1234'

1, Email, yak@yak.com
1, UserId, 3
1, Password, 1234
2, Email, yak@yak.com
2, UserId, 4
2, Password, 1234
3, ...
3, ... 
3, ...
4, ...
4, ...
4, ...

;with cte_Cross (r, a, av, b, bv, c, cv)
as	(	select	row_number()over(order by a.InputName), 
			a.InputName, 
			a.InputValue, 
			b.InputName, 
			b.InputValue, 
			c.InputName, 
			c.InputValue
		from	@Stage a
		cross
		join	@Stage b
		cross
		join	@Stage c
		where	a.InputName = 'A' and 
			b.InputName = 'B' and 
			c.InputName = 'C'
	)

select  r [Iteration], InputName, InputValue
from	cte_Cross
cross 
apply	(values	(a, av), 
		(b, bv), 
		(c, cv)	
	) as [d] (InputName, InputValue)

-- unpivot multi columns from http://bradsruminations.blogspot.com/2010/02/spotlight-on-unpivot-part-1.html

declare @inner_columns varchar(max), @outer_columns varchar(max),@sql varchar(max), @select varchar(max),@where varchar(max),
@cte varchar(max), @cte_columns varchar(max)
select @sql='', @inner_columns='', @select='', @where ='',@outer_columns='', @cte='', @cte_columns=''

select 
@inner_columns=@inner_columns+'['+sno+'].InputName as InputName'+sno+',['+sno+'].InputValue as InputValue'+sno+',', 
@outer_columns=@outer_columns+'InputName'+sno+',InputValue'+sno+',', 
@sql=@sql+'#stage ['+sno+'] cross join ',
@cte=@cte+'['+sno+'],'+ '['+sno+'v],',
@cte_columns=@cte_columns+'(['+sno+'],'+ '['+sno+'v]),',
@where=@where+'InputName'+sno+'='''+t.Inputname+''' and ' from
(select distinct Inputname,cast(dense_rank() over (order by inputname) as varchar(10)) as sno from #stage ) as t

select 
@inner_columns=left(@inner_columns,len(@inner_columns)-1),
@outer_columns=left(@outer_columns,len(@outer_columns)-1),
@cte=' ;with cte_cross (r,'+left(@cte,len(@cte)-1)+')',
@cte_columns=left(@cte_columns,len(@cte_columns)-1),

@sql=left(@sql,len(@sql)-11),
@where=left(@where,len(@where)-4)
select @sql='select row_number() over(order by [1].InputName) as sno,'+@inner_columns+' from '+
@sql
select @sql=' select row_number() over (order by Inputname1) as r,'+@outer_columns+' from ('
+@sql+') as t where '+@where
select @sql=@cte+' as ('+@sql+')'

select @sql=@sql+'
select  r [Iteration], InputName, InputValue
from	cte_Cross
cross 
apply	(values	'+@cte_columns +') as [d] (InputName, InputValue)'

exec(@sql)

quote:

---

Originally posted by nathans

There can never be too much dynamic sql :) Cool, Madhi. Thank you.

Im still convinced there is a mathematical way to solve this one, I have a solution based on multiset combinations that seems close, but is not yet working.

---

DECLARE	@Sample TABLE
	(
		i INT IDENTITY(1, 1),
		InputName VARCHAR(50),
		InputValue VARCHAR(50)
	)

INSERT	@Sample
	(
		InputName,
		InputValue
	)
SELECT	'A', '1' UNION ALL
SELECT	'A', '2' UNION ALL
SELECT	'B', '3' UNION ALL
SELECT	'B', '4' UNION ALL
SELECT	'C', '5' UNION ALL
SELECT	'D', '7'

;WITH cteSource (Iteration, InputName, recID, Items, Unq, Yak)
AS (
		SELECT		v.Number + 1 AS Iteration,
				s.InputName,
				ROW_NUMBER() OVER (ORDER BY v.Number, s.InputName) - 1 AS recID,
				s.Items,
				u.Unq,
				f.Perm AS Yak
		FROM		(
					SELECT		s.InputName,
							COUNT(*) AS Items
					FROM		@Sample AS s
					GROUP BY	s.InputName
				) AS s
		CROSS JOIN	(
					SELECT	COUNT(DISTINCT InputName) AS Unq
					FROM	@Sample
				) AS u
		INNER JOIN	master..spt_values AS v ON v.Type = 'P'
		OUTER APPLY	(
					SELECT TOP(1)	CAST(EXP(SUM(LOG(COUNT(*))) OVER ()) AS BIGINT)
					FROM		@Sample AS w
					WHERE		w.InputName >= s.InputName
					GROUP BY	w.InputName
					HAVING		COUNT(*) > 1
				) AS f(Perm)
		WHERE		v.Number < (SELECT TOP(1) EXP(SUM(LOG(COUNT(*))) OVER()) FROM @Sample AS x GROUP BY x.InputName)
)
SELECT		s.Iteration,
		s.InputName,
		w.InputValue		
FROM		cteSource AS s
CROSS APPLY	(
			SELECT	x.InputValue,
				ROW_NUMBER() OVER (ORDER BY x.InputValue) - 1 AS recID
			FROM	@Sample AS x
			WHERE	x.InputName = s.InputName
		) AS w(InputValue, recID)
WHERE		COALESCE(s.recID / (s.Yak * s.Unq / s.Items), 0) % s.Items = w.recID
ORDER BY	s.Iteration,
		s.InputName

declare @Stage table (i int identity(1,1), Ordinal int, InputName varchar(50), InputValue varchar(50))
insert into @Stage (Ordinal, InputName, InputValue)
	select 1, 'A', '1' union all select 1, 'A', '2' union all
	select 2, 'B', '3' union all select 2, 'B', '4' union all
	select 3, 'C', '5' union all
	select 4, 'D', '6' --union all select 4, 'D', '7'

declare @f int,
		@GroupSize int,
		@RowCount int

select	@GroupSize = count(distinct InputName) from @Stage

select	@Rowcount = @GroupSize*(cast(round(coalesce(exp(sum(log(abs(nullif(cnt,0))))),0),0) as int))
from	(select count(*) [cnt] from @Stage group by InputName) as [d]

declare @iStage table (InputName varchar(50), Cnt int)
;with c_Cnt 
as	(	select	a.InputName [a], isnull(b.cnt, 1) [cnt]
		from	@Stage a
		left
		join	(select Inputname, count(*) [cnt] from @Stage group by InputName ) b on
				a.inputname <> b.inputname	
		group 
		by		a.inputname, b.inputname, b.cnt
	)
insert into @iStage
	select	a, cast(round(coalesce(exp(sum(log(abs(nullif(cnt,0))))),0),0) as int)
	from	c_cnt
	group 
	by		a

declare @Grouping table (r int, c int, o int)
declare @Mapping table (r int, InputName varchar(50), InputValue varchar(50))


;with c_Groups
as	(	select	n [gn] 
		from	skynet_share.dbo.Number 
		where	n <= (@RowCount/@GroupSize)
	)
insert into @Grouping (r, c, o)
select	row_number() over(order by c, (@GroupSize*gn)+c),
		c, 
		((@GroupSize*gn) + c )-@GroupSize
from	c_Groups 
cross
join	(	select	n[c] 
			from	skynet_share.dbo.Number 
			where	n <=@GroupSize
		)d 

insert into @Mapping (r, InputName, InputValue)
	select	row_number() over(order by InputName, Alt, InputValue),
			InputName, 
			InputValue
	from	(	select	row_number() over(order by s.InputName, s.InputValue)% case when s.Ordinal%2 = 1 then 1 else iss.Cnt end as [Alt],
						s.InputName, 
						s.InputValue 
				from	@Stage s 
				inner 
				join	@iStage iss on 
						s.InputName = iss.InputName
				cross 
				apply	skynet_share.dbo.number n 
				where	iss.Cnt >= n.n
			) as [d]

select	InputName, InputValue--, *
from	@Grouping g
join	@Mapping m on
		g.r = m.r
order
by		g.o asc


-- product calc from mladen: http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx